CODEWITHSUNDEEP
excel
Mighty Tom
Mighty Tom

Reputation: 3

Excel: Search if multiple texts exist in a cell

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

Upvotes: 0

Views: 269

Answers (3)

tigeravatar
tigeravatar

Reputation: 26660

Alternate formula solution:

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

Upvotes: 0

chancea
chancea

Reputation: 5968

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.

Upvotes: 0

fraggle
fraggle

Reputation: 416

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

Upvotes: 1

Related Questions