vlookup sound alike

[dk]

Can we make a vlookup when lastname may have slightly differnt spelling in
middle characters?

 

[Gord Dibben]

Excel doesn't do fuzzy logic very well.

Could you use a wildcard or two?

=VLOOKUP("jo*n*",A1:B10,2,FALSE)

Will find johnson, johnston, johnstone, jones, jobergen


Gord Dibben MS Excel MVP

 

[Dave]

Hi dk'
If you have the fourth argument of your VLOOKUP set to FALSE or 0 (zero),
then spelling variations will return a #N/A.
If you set the 4th argument to TRUE or 1, or omit it altogether, then
VLOOKUP will return something from your lookup column when there is a
miss-spelling, but you can't guarantee it'll be the name you want. Also, to
have any chance of returning the right name, the lastname column needs to be
sorted alphabetically when using the TRUE argument.
Regards - Dave.

 

[dk]

is there anyway to do it only by all first letters & last letters but
without having to go thru the whole ABC?

 

[Roger Govier]

Hi

Just modify what Gord gave you
=VLOOKUP("j*n*",A1:B10,2,FALSE)

This will find anything starting with "j" and ending with "n"

 

[Dave Peterson]

I bet you meant to remove the asterisk after the "n".

=VLOOKUP("j*n",A1:B10,2,FALSE)

 

[Roger Govier]

A sound bet, Dave.
You would have won!!!