Vlookup stumped!!

[TonyforVBA]

Can anyone explain whats gone wrong in the attached example. I've been messing around with this the past half hour and cant see any reason for the error!

[ragulduy]

Hi - vlookup will look at the first column of the lookup range to match the search term.

So you are telling it to look in column L for the unit, which is stored in column M. Therefore it doesn't find anything and returns an error.

In addition, you are not using absolute references, so the relative reference adjusts as you copy the formula down, meaning the lookup table is changing.

You need to copy the column L data to column N and change your formula to:
=VLOOKUP(C2,$M2:$N6,2,FALSE)

[sktneer]

If you don't want to change the data layout in your sheet as per the suggestion given by ragulduy, you can try the following formula in B2 and copy down.

Please Login or Register  to view this content.

[martindwilson]

or
=VLOOKUP(C2,CHOOSE({1,2},M2:M6,L2:L6),2,FALSE)

[AZ-XL]

Hi
In vlookup function the value you are looking up have to be in the first column of the table array. That is why it gives an error.
=IFERROR(INDEX(L:L,MATCH(C2,M:M,0)),"")

[twiggywales]

you can also use CHOOSE to do a left lookup

=VLOOKUP($C2,CHOOSE({1,2},$M$2:$M$6,$L$2:$L$6),2,0)

but its better to go with index.