Vlookup or similar function & "Contains"

[hellomynameiskristin]

I am looking for a vlookup or similar function where the lookup value may be in a different form. Meaning.

In my lookup value, it could be: John Smith.

But in the first column in my aray, it could be J. Smith/Smith, John/or John Smith.

So I would like to on my lookup value page, creat a new column (since thats the shorter list) where I will just put their last name, and then have a vlookup/type formula that will look for values that CONTAIN "Smith"

any help GREATLY appreciated!

 

[hellomynameiskristin]

And to update, I tried a formula with a wild card:
=VLOOKUP("*W10*",Lookuparray,2,0)

[W10 is my lookup cell that contains "Smith"]

and it doesn't work...

 

[T. Valko]

And to update, I tried a formula with a wild card:
=VLOOKUP("*W10*",Lookuparray,2,0)

[W10 is my lookup cell that contains "Smith"]

and it doesn't work...

If W10 = Smith try it like this...

=VLOOKUP("*"&W10&"*",Lookuparray,2,0)

 

[hellomynameiskristin]

Updated! I answered my own question! HAHA!

for those in google world that might stumble upon this.

Your lookup value needs the asterick, not the look up cell in your formula! (slaps forehead)

so in my look up cell I put *Smith* and my regular vlookup works.

 

[hellomynameiskristin]

If W10 = Smith try it like this...

=VLOOKUP("*"&W10&"*",Lookuparray,2,0)

AWESOME! That works too!

 

[Just Jan]

Try this for mutiple returns
A1 is lookup value
B1 =INDEX(D:D,MATCH(SMALL(E:E,ROW(E1)),E:E,0)) copy down a few rows
Col D is list of names
E1 =IF(ISNUMBER(SEARCH(A1,D1)),ROW(),"") copy down to match col D.
If you want exact spelling change SEARCH to FIND

My 1st answer on this forum. Hope it helps.