Vlookup with wildcard

Hi All,

I stuck while working on Vlookup with wildcard thing. Actually i have two column, one is "Name" and second is "Amt". In "Name" column i mentioned "Harindra Gopal Devadiga" and in "Amt" column i mentioned "100".

Same way i have another two column with same names, "Name" and "Amt", now in "Name" column i mentioned "Harindra Devadiga" i skipped Gopal from it. Now what i want is with the help of VLOOKUP with WILDCARD i have to bring that same Amt.

For reference i attached the file in which the red color cell have a formula "=VLOOKUP("*"&D2&"*",A2:B2,2,FALSE)". Can anyone help me out in this.

Try this ...

=VLOOKUP("*"&SUBSTITUTE(D2," ","*")&"*",A2:B2,2,FALSE)

Hi Phuocam,

Its working fine for me, but there are some other scenario i was facing issue

For Eg: if name is Sandeep Reddy Peddhi in A2 and if i type name in reverse (Peddhi Reddy Sandeep) it get an #N/A and if i type Peddhi S it also give an same #N/A in D2
error.

Can you please help me out in this