Vlookup strings - Different types of matches in Data

[Vijay123]

Hi all,

I am struggling to figure out how to String Vlookup based on wildcards. Please see the image I attached to understand the problem.

As you can see from the red highlighted text, I want to look up text in A10 in the table in P, Q columns. The cell A10 contains "Mahendra Singh Dhoni". However, my table has "MS Dhoni" in it. Hence the formula doesn't return the required value.

With Wildcards,  I have used the Vlookup formula =IFNA(VLOOKUP("*"&A10&"*",$P$2:$Q$101,2,FALSE),"0")

I understood that if my table has any text before and after "Mahendra Singh Dhoni", it will return the value as per CONTAINS logic.

Is there a way to get the value if one word in A10 matches with that in the table? (i.e. to match based on the word 'Dhoni' in A10)

Thanks in advance!

VJ

[#2]

@Vijay123 

A very difficult lookup because the lookup value contains more text than what you're looking for in the table array.  Additionally, there's the "MS" abbreviation.  I think what you're left with is using extraction formulas (LEFT, RIGHT, MID) with wildcards to do the lookup - it's very risky.

[#3]

@Vijay123 

To supplement what @Patrick2788 has already said, your situation is a good argument for always designing data tables--when names are concerned--such that there are separate columns for FIRST, MIDDLE and LAST names, rather than putting them all together in one. It's far easier to do all kinds of things with them separated (for example, sort alphabetically by last name; assemble lists alphabetically by last name; print name name cards with first name first, etc.

If I were you in this case, assuming you are going to want to do this kind of lookup with all the names, why don't you take time, using LEFT, MID, and RIGHT functions, to break the names apart into separate columns.