Results 1 to 2 of 2

Thread: Comparing two lists, of similar but different text

  1. #1

    Comparing two lists, of similar but different text



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,491
    Articles
    0
    Excel Version
    Excel 2016
    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....


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •