vlookup with left function

**bronkista** · 06-22-2011, 11:15 AM

Hi,

I'm using Vlookup to lookup the first 2 letters of a name, but it's somehow displaying a different result.
I might be missing something with the formula, but I can't figure it out.

TIA.

**Domski** · 06-22-2011, 11:29 AM

Hi,

Try: =INDEX($B$1:$B$4,MATCH(LEFT(D1,2),LEFT($A$1:$A$4,2),0))

Entered in E1 and confirmed with Ctrl+Shift+Enter as it's an array formula and then copied down.

Dom

**daddylonglegs** · 06-22-2011, 11:40 AM

Try using a wildcard.....and 4th argument of VLOOKUP should be zero

=VLOOKUP(LEFT(D1,2)&"*",$A$1:$B$4,2,0)

**bronkista** · 06-22-2011, 11:51 AM

Thank you very much Domski and daddylonglegs!

Both your suggestions worked great.

Though I was just wondering why wasn't the formula working, I'd appreciate it if you could explain... but if not, thanks again for the help.

**daddylonglegs** · 06-22-2011, 02:53 PM

Your formula was this

=VLOOKUP(LEFT(D1,2),$A$1:$B$4,2,2)

It's not usual to use 2 as the final argument, the options are TRUE (1) or FALSE (0). If you use 2 it behaves like 1.

When you use 1 as the final argument the lookup range (A1:A4 here) needs to be sorted ascending. As A1:A4 isn't sorted ascending the results are "odd". If you use this version instead

=VLOOKUP(LEFT(D1,2),$A$1:$B$4,2,0)

Then you'd get #N/A for your examples because you are trying to find a match for "De" or "Kr" in A1:A4 and neither of those values exist.

With my suggestion you are looking for "De*" and "Kr*" where * is a wildcard representing zero or more characters....so you do get matches for those