LOOKUP returning wrong values


R

Raghavendran

Hi,

I am using Microsoft Excel 2004 for mac version 11.3.5
I encounter strange problem of LOOKUP returning incorrect results. Following
is the simplified version of data and the problem.

TRUE A A
FALSE P R
FALSE F N
FALSE C D
TRUE C C
FALSE P Q
TRUE E E
TRUE G G
TRUE H H
TRUE I I
FALSE K L
TRUE K K
TRUE M M
FALSE E F
TRUE P P
TRUE S S
TRUE T T
TRUE W W
TRUE Y Y
FALSE T V

In column 3, you will find Data array.
column 2 is the result of the formula = =VLOOKUP($C3;$C$3:$C$22;1)
column 1 is the result of the formula = EXACT($B3;$C3)

The problem with VLOOKUP (also with LOOKUP, HLOOKUP) is it recognizes texts
R,N,D,Q,L,F,V as P,F,C,P,K,E,T respectively. The above misrecognition seems
to be only with the function LOOKUP as the function EXACT correctly
identifies mismatches in texts in column 2 and column 3 in the above example.

Any idea of the source and/or correction for the above problem with LOOKUP
function will be deeply appreciated.

regards
raghav

P.S : The problem is reproducable with Microsoft Excel 2002, Windows XP SP2.
 
Ad

Advertisements

R

Ron Coderre

Check Excel Help for VLOOKUP and HLOOKUP.
Those functions have a 4th argument, range_lookup, that dictates the type of
match.
If that argument is TRUE or omitted, those formulas use an approximate
match. However, the list must be sorted in ascending order. Your list is not
sorted.

If you add the 4th arugment value of FALSE (or 0) you'll get the correct
values returned.

The LOOKUP function requires the list to be sorted.

Does that help?

Regards,

Ron Coderre
Microsoft MVP (Excel)
 
Ad

Advertisements

R

Raghavendran

Hi Ron,

Thanks for the quick response, Sorting the array solved the problem. I
didnot check the fourth argument.

Thanks a lot
raghav
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top