0

I have three columns in excel: FirstName, LastName, and a third column that contains first name, last name and maybe other characters.

Now what I want to do is search in the third column, and if both FirstName and LastName exist in a certain cell (since names in the third column is not in the same order as the first and second column) in the third column, return 1. Otherwise, return 0. The order of FirstName and LastName in the third column should not affect the result.

E.g. Jennifer, Smith, Smith Jennifer > this will return 1, regardless of the first/last name order

Anyone knows how to accomplish this with a formula?

Many Thanks,

To make my question more clear, here is an example.

First Name, Last Name, Name List

Jennifer , Smith , Smith Jennifer

Richard, Borland, Richard Borland (acc) <-this cell has other characters

Mike , Leanne, Tom Jackson

Tom, Jackson , Leanne Mike <-The third columns in the last two rows do not match the order of the first two columns

Improve this question
2
  • What other characters could you potentinally have? Could you have " Smith, Jennifer" in third column? Commented Dec 8, 2014 at 21:19
  • @user3885927, it could have any possible characters, like space, comma, or people's titles Commented Dec 8, 2014 at 22:07

3 Answers 3

Reset to default
1

The formula you need is:

=IF(AND(NOT(ISERROR(FIND(A1,C1))),NOT(ISERROR(FIND(A1,C1)))),1,0)

Assuming Jennifer is in A1 and Smith is in B1

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

@chancea Unusual for me not to have fat fingers slowing me down!
Hi @fraggle, thanks for replying. Your solution is very creative. It works well when the third column contains only first and last name, but it becomes problematic when there are other text in the third column. For example, if there is a middle name in between first and last name in the third column, it will result in 0. Does it make sense to you?
Forgive me if I am wrong here, since I am really new to excel. It looks like that the formula only compares A1 and B1 against C1, not column C, does it?
Re: middle names - no the formula will find both strings even if there are middle names, as long as those two strings exist anywhere in the third cell it will result intrue.
0

NEW

(this searches the entire 3rd column)

So basically it took some tweaking and googling but I think I found a solution that work. Here is the simple version of the new formula, note that this formula is case sensitive and there really isn't anything I can do about that since you cannot say UPPER(C:C) (UPPEPR() needs a single cell). But this does work even for cells that have junk before, between, or after the joined name (and of course the name order does not matter)

=IF((COUNTIF(C:C,"*"& A1 & "*" & B1 &"*") + COUNTIF(C:C,"*"& B1 & "*" & A1 &"*"))>0,1,0)

of course as I said in my old answer, change up the references as needed.

(Small edit) I just realized this also will check part of names. So like if someone is named "James Will" and another named "James William" and the name "James William" exists in column 3 it will show 1 for both "James Will" and "James William"

OLD

(this searches the adjacent cell in the 3rd column)

If you are okay with having the result in a 4th column you can use a formula like 

=IF(ISERROR(FIND(A1 & " " & B1,C1)),IF(ISERROR(FIND(B1 & " " & A1,C1)),0,1),1)

And then just auto fill down.

Of course if your data is not in columns A-C just fix the cell references.

Now the FIND formula is case sensitive. And this example uses 1 space between the first and last (or last and first) names. If the extra characters you explained happen to appear between the names this will not work. If that is such the case you could then use this formula:

=IF(AND(NOT(ISERROR(FIND(A1,C1))),NOT(ISERROR(FIND(B1,C1)))),1,0)

And again, autofill down, change cell refrences as needed.

Improve this answer

11 Comments

Mind you, your answer is more extensive than mine and covers possible issues that mine ignores. I guess if case sensitivity is an issue you could use UPPER () or LOWER () on all the string arguments to ignore the case?
@fraggle Yeah actually I don't know why I didn't think of using UPPER() or LOWER() because yeah that would solve the case problem.
Man, I never even considered the case problem. Tip of the hat to you for bringing it up.
Thanks @chancea, same question here, does the formula only compares two cells against one cell instead of the entire third column?
@MightyTom Sorry we both interpreted your question incorrectly. To iterate over the entire column is a much harder task. I can look to see if its even possible without VBA but off the top of my head I am not too confident that it is..(gonna take some time so don't expect anything for a while :D)
|
0

Alternate formula solution:

=--(SUMPRODUCT(COUNTIF(C1,"*"&A1:B1&"*"))=2)
Improve this answer

1 Comment

this formula doesn't work under some circumstances like: first name:Chae, last name:Darin, and the last column has name Michael Darinn that contains the exact same characters.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.