Name Matching

Here I have a spreadsheet that contains three columns of names. Each column has different lengths of data and the name format is not the same across the board. Two of the lists list the last name first and the the main list I am looking at (the middle one) shows the first name first.

If it is possible, I need to find a way to see which names on the third list appear on the first two. Is that possible when the format is not the same for all columns? If so, an app or formula would be appreciated. Example List.xlsx

Put this formula in D1:

=IF(C1="","",IF(ISNA(MATCH(RIGHT(C1,LEN(C1)-SEARCH(",",C1)-1)&" *"&LEFT(C1,SEARCH(",",C1)-1),B:B,0)),IF(ISNA(MATCH(SUBSTITUTE(C1," ","")&"*",A:A,0)),"Not present","List A"),"List B"))

then copy down to the bottom of your data in column C.

Note that your list in column A generally has surname,forname initial, whereas the names in column C do not have the initial, so the inner MATCH function looks for a partial match (and removes the space after the comma). The outer MATCH function rearranges the name and looks at column B for a partial match, with possibly an initial after the first name.

Hope this helps.

Pete

Originally Posted by Spartan1138

Thanks people, for all your help! I'm using Pete's formula, and I have a question. If an item on C is on both A and B, will the formula say so?

No, my formula looks first at column B to find a match (as you said that was the main list), then it looks at column A, and if there is no match then it returns "Not Present". There didn't seem to be any point in seeing if it was in BOTH lists.

Hope this helps.

Pete

I was referring to the case sensitivity, but aside from that, absolutely nothing. Thanks guys!

That's even better. Thank you!

Hi Imvk99,

why do you use FIND (which is case-sensitive) and then convert everything to LOWER, when you can use SEARCH (which is not case-sensitive, and which allows you to use wildcards if you wish)?

Pete

Originally Posted by lmvk99

Good point, bad habit

It's just that I generally do not use FIND because of that restriction, and only use it where case is important.

Incidentally, MATCH is not case-sensitive either, so your point doesn't apply to the first two solutions.

Pete

Originally Posted by Spartan1138

Thanks people, for all your help! If an item on C is on both A and B, will the formula say so?

If you really want this, then you could use this variation of my original formula:

=IF(C1="","",SUBSTITUTE(SUBSTITUTE(IF(ISNA(MATCH(SUBSTITUTE(C1," ","")&"*",A:A,0)),"-","List A ")&IF(ISNA(MATCH(RIGHT(C1,LEN(C1)-SEARCH(",",C1)-1)&" *"&LEFT(C1,SEARCH(",",C1)-1),B:B,0)),"-","List B"),"--","Absent"),"-",""))

which will give you the options of "List A", "List "B", "List A List B" or "Absent".

If you want the matching items to stand out and not have the word "Absent", you can use this slightly simpler formula:

=IF(C1="","",IF(ISNA(MATCH(SUBSTITUTE(C1," ","")&"*",A:A,0)),"","List A ")&IF(ISNA(MATCH(RIGHT(C1,LEN(C1)-SEARCH(",",C1)-1)&" *"&LEFT(C1,SEARCH(",",C1)-1),B:B,0)),"","List B"))

However, these formulae do mean that you have to lookup against two lists all the time, so the formula could take longer to execute.

Anyway, hope this helps - is the thread Solved now?

Pete

Originally Posted by lmvk99

Wierd. Cos when I tested it I found it missed an entry that was all CAPS, I just assumed it was a case issue.

With so many names, I don't know how you would have spotted that. Can you remember which name it was?

Pete