Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP returning wrong values
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP returning wrong values
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) "Raghavendran" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP returning wrong values
Hi Ron,
Thanks for the quick response, Sorting the array solved the problem. I didnot check the fourth argument. Thanks a lot raghav "Ron Coderre" wrote: 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) "Raghavendran" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Returning Multiple Values in a lookup | Excel Worksheet Functions | |||
Problem Returning Mulitple Lookup Values | Excel Worksheet Functions | |||
Lookup function gives wrong values occasionally | Excel Discussion (Misc queries) | |||
Excel 2002 Lookup formula returning wrong results? | Excel Worksheet Functions | |||
Returning all values from a lookup - not just the first/last one | Excel Worksheet Functions |