Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can "=LOOKUP(I4,I2:I11)" POSSIBLY return #N/A??
The subject pretty much says it all, this is happening, and there's a valid,
plain integer value in I4, and manually entering said value does the same thing... I don't get it. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
...
A little extra that REALLY makes it weird. What I'm actually using is
=LOOKUP(SMALL(I2:I11,1),I2:I11,A2:A11), I just tried the above to narrow down the problem. Using ANY of the values besides 1st smallest works just fine with these arrays, including returning the value in A2 with =LOOKUP(SMALL(I2:I11,2),I2:I11,A2:A11). Using =LOOKUP(SMALL(I2:I11,1),I3:I11,A3:A11) WORKS FINE! It's only when the array includes I2 that it screws up looking for I4.. and ONLY screws up when looking for I4 specifically. !?! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
...
The values in lookup_vector/array (your I2:I11) must be placed in ascending
order. Is it? This is probably the root cause behind the phenomena you are experiencing. -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:300 Subscribers:70 xdemechanik --- "Jerry Mitchell" wrote: A little extra that REALLY makes it weird. What I'm actually using is =LOOKUP(SMALL(I2:I11,1),I2:I11,A2:A11), I just tried the above to narrow down the problem. Using ANY of the values besides 1st smallest works just fine with these arrays, including returning the value in A2 with =LOOKUP(SMALL(I2:I11,2),I2:I11,A2:A11). Using =LOOKUP(SMALL(I2:I11,1),I3:I11,A3:A11) WORKS FINE! It's only when the array includes I2 that it screws up looking for I4.. and ONLY screws up when looking for I4 specifically. !?! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can "=LOOKUP(I4,I2:I11)" POSSIBLY return #N/A??
On Sun, 24 May 2009 00:20:00 -0700, Jerry Mitchell <Jerry
wrote: The subject pretty much says it all, this is happening, and there's a valid, plain integer value in I4, and manually entering said value does the same thing... I don't get it. Maybe your data in the "array", I2:I11 in your case, is not sorted. From the help text of the LOOKUP function: The values in array must be placed in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent. Hope this helps / Lars-Åke |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can "=LOOKUP(I4,I2:I11)" POSSIBLY return #N/A??
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
...
On Sun, 24 May 2009 00:34:01 -0700, Jerry Mitchell
wrote: A little extra that REALLY makes it weird. What I'm actually using is =LOOKUP(SMALL(I2:I11,1),I2:I11,A2:A11), I just tried the above to narrow down the problem. Using ANY of the values besides 1st smallest works just fine with these arrays, including returning the value in A2 with =LOOKUP(SMALL(I2:I11,2),I2:I11,A2:A11). Using =LOOKUP(SMALL(I2:I11,1),I3:I11,A3:A11) WORKS FINE! It's only when the array includes I2 that it screws up looking for I4.. and ONLY screws up when looking for I4 specifically. !?! You should keep your information all in the same thread. See your original for the problem and possible solutions. --ron |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can "=LOOKUP(I4,I2:I11)" POSSIBLY return #N/A??
On Sun, 24 May 2009 08:05:15 -0400, Ron Rosenfeld
wrote: On Sun, 24 May 2009 00:20:00 -0700, Jerry Mitchell <Jerry wrote: The subject pretty much says it all, this is happening, and there's a valid, plain integer value in I4, and manually entering said value does the same thing... I don't get it. If I2:I11 is not sorted in ascending order, you can certainly get #N/A values. In some quick testing with Excel 2007, it seems to be the case that if I4 contains a lower value than I2 and I3, LOOKUP will return #N/A. If your values are not sorted, and you are looking for an exact match, try VLOOKUP instead: =VLOOKUP(I4,I2:I11,1,TRUE) --ron FALSE would be a better choice for range_lookup if the values are non sorted. / Lars-Åke |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can "=LOOKUP(I4,I2:I11)" POSSIBLY return #N/A??
On Sun, 24 May 2009 13:17:26 GMT, Lars-Åke Aspelin
wrote: FALSE would be a better choice for range_lookup if the values are non sorted. / Lars-Åke Typo. You are, of course, correct. --ron |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can "=LOOKUP(I4,I2:I11)" POSSIBLY return #N/A??
On Sun, 24 May 2009 11:45:01 -0400, Ron Rosenfeld
wrote: On Sun, 24 May 2009 13:17:26 GMT, Lars-Åke Aspelin wrote: FALSE would be a better choice for range_lookup if the values are non sorted. / Lars-Åke Typo. You are, of course, correct. --ron Not. Brain typo maybe. I guess you call that one a bubble sort. :-) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can lookup return cell reference istead of "text" for sumif? | Excel Worksheet Functions | |||
function to return day in the form "Monday", "Tuesday" etc given . | Excel Worksheet Functions | |||
Check if cells contain the word "Thailand", return "TRUE" | Excel Worksheet Functions | |||
If (A1 = "ValueA" & C1 = "ValueB") return the quantity of matches | Excel Discussion (Misc queries) | |||
vlookup problem, possibly due to "noise" | Excel Worksheet Functions |