Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
vlookup sound alike
Can we make a vlookup when lastname may have slightly differnt spelling in
middle characters? |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
vlookup sound alike
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 On Thu, 1 May 2008 16:00:24 -0700, dk wrote: Can we make a vlookup when lastname may have slightly differnt spelling in middle characters? |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
vlookup sound alike
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" wrote: Can we make a vlookup when lastname may have slightly differnt spelling in middle characters? |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
vlookup sound alike
is there anyway to do it only by all first letters & last letters but
without having to go thru the whole ABC? "Dave" wrote: 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" wrote: Can we make a vlookup when lastname may have slightly differnt spelling in middle characters? |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
vlookup sound alike
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" -- Regards Roger Govier "dk" wrote in message ... is there anyway to do it only by all first letters & last letters but without having to go thru the whole ABC? "Dave" wrote: 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" wrote: Can we make a vlookup when lastname may have slightly differnt spelling in middle characters? |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
vlookup sound alike
I bet you meant to remove the asterisk after the "n".
=VLOOKUP("j*n",A1:B10,2,FALSE) Roger Govier wrote: 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" -- Regards Roger Govier "dk" wrote in message ... is there anyway to do it only by all first letters & last letters but without having to go thru the whole ABC? "Dave" wrote: 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" wrote: Can we make a vlookup when lastname may have slightly differnt spelling in middle characters? -- Dave Peterson |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
vlookup sound alike
A sound bet, Dave.
You would have won!!! -- Regards Roger Govier "Dave Peterson" wrote in message ... I bet you meant to remove the asterisk after the "n". =VLOOKUP("j*n",A1:B10,2,FALSE) Roger Govier wrote: 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" -- Regards Roger Govier "dk" wrote in message ... is there anyway to do it only by all first letters & last letters but without having to go thru the whole ABC? "Dave" wrote: 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" wrote: Can we make a vlookup when lastname may have slightly differnt spelling in middle characters? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sound with excel | Excel Discussion (Misc queries) | |||
Combining alike cells? | Excel Discussion (Misc queries) | |||
Sound File | Excel Worksheet Functions | |||
using formula to compare two text lists that are not alike and ma. | Excel Worksheet Functions | |||
Excel DB alike? | Excel Discussion (Misc queries) |