Partial Match for Vlookup

I have a lengthy post code file that is simply one column with the post code and the second column is the name. The format for the name can vary. I know this is a simple vlookup but it is not giving me data when I am using * and concatenate for wildcard before and after.

Sample file attached.

The formula I am using is =VLOOKUP("*"&F1&"*",A:B,1,0) where the partial lookup value is in field f1.

Try this in H1:
=INDEX($A$1:$A$4,AGGREGATE(15,6,ROW($B$2:$B$4)/(SEARCH("*"&F1&"*",$B$2:$B$4)),1))

By the way Vlookup can be applied only to the right of the value searched, not to the left.

Just take into account that if you search the word "Amembassy" that appears both in lines 2 & 3 the answer will bring the 1st match, meaning - '102'

Originally Posted by belinda200

Just take into account that if you search the word "Amembassy" that appears both in lines 2 & 3 the answer will bring the 1st match, meaning - '102'

Belinda,

Thanks fir letting me know the options. I will just move the code column to be the 2nd column and use my original formula.

Hank

Belinda,

Thanks fir letting me know the options. I will just move the code column to be the 2nd column and use my original formula.

Belinda,

Thanks fir letting me know the options. I will just move the code column to be the 2nd column and use my original formula.