How can "=LOOKUP(I4,I2:I11)" POSSIBLY return #N/A??

[Jerry Mitchell]

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.

 

[Jerry Mitchell]

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. !?!

 

[Max]

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

 

[Lars-Åke Aspelin]

On Sun, 24 May 2009 00:20:00 -0700, Jerry Mitchell <Jerry

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

 

[Ron Rosenfeld]

On Sun, 24 May 2009 00:20:00 -0700, Jerry Mitchell <Jerry

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

 

[Ron Rosenfeld]

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

 

[Lars-Åke Aspelin]

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

 

[Ron Rosenfeld]

FALSE would be a better choice for range_lookup if the values are non
sorted.

/ Lars-Åke

Typo. You are, of course, correct.
--ron

 

[FatBytestard]

Typo. You are, of course, correct.
--ron

Not. Brain typo maybe. I guess you call that one a bubble sort.