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. |
...
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. !?! |
...
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. !?! |
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 |
How can "=LOOKUP(I4,I2:I11)" POSSIBLY return #N/A??
|
...
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 |
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 |
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 |
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. :-) |
All times are GMT +1. The time now is 02:41 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com