Comparing two lists, of similar but different text

F

FierceEffect

New member
Joined
Nov 14, 2012
Messages
1
Reaction score
0
Points
0
Hi everyone, I was hoping for some assistance for making comparisons in a list where basic vlookup isnt working for me. I have two lists, both of music artists/bands. I'm trying to find out if artists in Column B are included in the Column A list. My issue is that the first list might have an artists as "ORTON, BETH - COMFORT OF STRANGERS - 0946355414", and the second list would have "Beth Orton" somewhere in it. What formula can I use to find if "Beth Orton" from column B is in Column A. Attached is a little excel example file to work with. -Thanks!
 

Attachments

  • sample.xlsx
    7.9 KB · Views: 14
Try:

=AND(ISNUMBER(MATCH("*"&TRIM(MID(SUBSTITUTE(B1," ",REPT(" ",100)),1,100))&"*",A:A,0)),ISNUMBER(MATCH("*"&TRIM(MID(SUBSTITUTE(B1," ",REPT(" ",100)),100,100))&"*",A:A,0)))

copied down

This returns TRUE if a match of name is found.... Note: It returns FALSE if for instance there is a Minogue without the Kylie....
 
You must log in or register to reply here.
Back
Top