Vlookup Accuracy problem

[Mervyn Thomas]

I have a sorted look up range with codes such as these in the first column:
LPM1
LPM2
LPM3
LPMM1

However when I do a Vlookup using "vlookup(LPMM1, lookuprange,2,TRUE) it is
returning the value for LPM3.

Can you suggest what is happening and how to correct? Is there a more
precise function?

Mervyn

 

[Trevor Shuttleworth]

Mervyn

works for me.

=VLOOKUP(C1, lookuprange,2,TRUE) in cell B1 and with LPMM1 in cell C1.

lookuprange is defined as =Sheet3!$A$2:$B$5

Regards

Trevor

 

[Ken Wright]

Just as an addendum, unless you are specifically happy with returning 'closest
to' matches, then you should be using FALSE or 0 as the 4th argument and not
TRUE. This will ensure that only an exact match yields a result.

 

[Andy Sandford]

Mervyn,

Just a thought, try this - VLOOKUP(LPMM1, lookuprange,2,FALSE)

I think it might be something to do with the way Excel searches the
lookuprange. With TRUE, VLOOKUP may not give the correct value. If FALSE,
lookuprange does not need to be sorted, and will return the EXACT value.

HTH

Andy

 

[Mervyn Thomas]

Thanks guys - it seems my problem arose from some embedded blanks in the
lookup file which confused the issue!! Once trimmed everything OK
Mervyn