Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Inconsistent results with =LOOKUP?
I am using Lookup across 2 worksheets.
Where lookup finds a match, the result vector seems to work fine (too many rows to check them all). However, when no match is found, the result vector either returns a '1' or (in one instance only) a #N/A error. I would have expected the error rather than the '1' or am I missing something? The formula in sheet "2005": =LOOKUP($B3,'2004'!B$3:B$9689,'2004'!P$3:P$9689) Any ideas? -- Mike Watkins -- Mike Watkins |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Inconsistent results with =LOOKUP?
Hi Mike,
The LOOKUP function does not usually return #N/A when there's no exact match. Rather, it goes to the next smallest value in the lookup vector and returns the corresponding result vector. The only time it returns #N/A is when your lookup value is smaller than the smallest value in the lookup vector. You might want to try using VLOOKUP instead, if you want to always return #N/A when there's no exact match. Hope this helps. -Simon "watkincm" wrote: I am using Lookup across 2 worksheets. Where lookup finds a match, the result vector seems to work fine (too many rows to check them all). However, when no match is found, the result vector either returns a '1' or (in one instance only) a #N/A error. I would have expected the error rather than the '1' or am I missing something? The formula in sheet "2005": =LOOKUP($B3,'2004'!B$3:B$9689,'2004'!P$3:P$9689) Any ideas? -- Mike Watkins -- Mike Watkins |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Inconsistent results with =LOOKUP?
Then LOOKUP was working correctly - that's exactly what it did... :)
VLOOKUP works perfectly for what I need - thanks for your help -- Mike Watkins "SiC" wrote: Hi Mike, The LOOKUP function does not usually return #N/A when there's no exact match. Rather, it goes to the next smallest value in the lookup vector and returns the corresponding result vector. The only time it returns #N/A is when your lookup value is smaller than the smallest value in the lookup vector. You might want to try using VLOOKUP instead, if you want to always return #N/A when there's no exact match. Hope this helps. -Simon "watkincm" wrote: I am using Lookup across 2 worksheets. Where lookup finds a match, the result vector seems to work fine (too many rows to check them all). However, when no match is found, the result vector either returns a '1' or (in one instance only) a #N/A error. I would have expected the error rather than the '1' or am I missing something? The formula in sheet "2005": =LOOKUP($B3,'2004'!B$3:B$9689,'2004'!P$3:P$9689) Any ideas? -- Mike Watkins -- Mike Watkins |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup on pivot table results = #N/A | Excel Worksheet Functions | |||
Inconsistent Array Count results | Excel Worksheet Functions | |||
abdualmohsn | Excel Discussion (Misc queries) | |||
calculating results in formulas | Excel Discussion (Misc queries) | |||
Inconsistent Results: Advanced Filter Unique Records Only | Excel Discussion (Misc queries) |