R
Robert Hatcher
=LOOKUP(32,F16:F30)
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
R
Robert Hatcher
Now I will complete the post. One of those days....
Im having a problem with lookup returning a value outside the range of
the array
In cells f1-f16 I have the values 16-30, one value in each cell in
accending order.
=LOOKUP(15,F16:F30) returns #NA as expected
=LOOKUP(25,F16:F30) returns 25 as expected
I expect =LOOKUP(35,F16:F30) to return #NA but it returns 30
So inputs less than the values in the array are NA and inputs within
the values in the array are returned. Inputs greater than the values
in the array should be NA but return the highest value in the array.
I get the same results with Vlookup.
Im at a loss why is this happening? More importanly what can I do
about it?
Thanks
Robert
Im having a problem with lookup returning a value outside the range of
the array
In cells f1-f16 I have the values 16-30, one value in each cell in
accending order.
=LOOKUP(15,F16:F30) returns #NA as expected
=LOOKUP(25,F16:F30) returns 25 as expected
I expect =LOOKUP(35,F16:F30) to return #NA but it returns 30
So inputs less than the values in the array are NA and inputs within
the values in the array are returned. Inputs greater than the values
in the array should be NA but return the highest value in the array.
I get the same results with Vlookup.
Im at a loss why is this happening? More importanly what can I do
about it?
Thanks
Robert
R
Ron Rosenfeld
Now I will complete the post. One of those days....
Im having a problem with lookup returning a value outside the range of
the array
In cells f1-f16 I have the values 16-30, one value in each cell in
accending order.
=LOOKUP(15,F16:F30) returns #NA as expected
=LOOKUP(25,F16:F30) returns 25 as expected
I expect =LOOKUP(35,F16:F30) to return #NA but it returns 30
So inputs less than the values in the array are NA and inputs within
the values in the array are returned. Inputs greater than the values
in the array should be NA but return the highest value in the array.
I get the same results with Vlookup.
Im at a loss why is this happening? More importanly what can I do
about it?
Thanks
Robert
This behavior is by design. From HELP:
---------------------------------------------------------
The LOOKUP function array form syntax has these arguments:
lookup_value Required. A value that LOOKUP searches for in an array. The lookup_value argument can be a number, text, a logical value, or a name or reference that refers to a value.
If LOOKUP can't find the value of lookup_value, it uses the largest value in the array that is less than or equal to lookup_value.
If the value of lookup_value is smaller than the smallest value in the first row or column (depending on the array dimensions), LOOKUP returns the #N/A error value.
----------------------------------------------------------
You have a few options if you want to have an error if the lookup_value is greater than the maximum value in the range.
=if(lookup_value>max(range),NA(), your_lookup_formula)
=IF(35>MAX(F1:F16),NA(),LOOKUP(35,F1:F16))
or, if you are always going to be looking for an exact match, you could use VLOOKUP with range_lookup set to FALSE.
=VLOOKUP(35,F1:F15,1,FALSE)
And I would suggest, in both formulas, that you replace your "35" by some cell reference where the cell contains 35.
P
Pete_UK
Hi Robert,
that is how LOOKUP works - the data has to be sorted and it will
return the location of the highest value if the sought item is larger
than the values.
You might consider using MATCH or VLOOKUP, as these have an optional
parameter which you can set to TRUE (or 1) (which is the default
setting and works as with LOOKUP) or to FALSE (or 0), and with this
setting you are looking for an exact match and the data does not need
to be sorted. So, an equivalent to your LOOKUP formula would be:
=MATCH(25,F16:F30,0) + 15
MATCH returns the relative position in the array, hence you need to
add 15 onto the result to get the value 25. If you try this with 15 or
35 instead of 25 you will get the #N/A error, and this can be trapped
using ISNA like this:
=IF(ISNA(MATCH(15,F16:F30,0)),"out of range",MATCH(15,F16:F30,0)+15)
which will now give an error message of your choosing - you could make
it "" (i.e. blank), or zero, depending on what you want to do with the
returned value. If you are using XL2007 or later you can use IFERROR
and avoid the repetition of the MATCH function.
Hope this helps, and check out your earlier post if you want me to
have a further look at the transducer problem.
Pete
that is how LOOKUP works - the data has to be sorted and it will
return the location of the highest value if the sought item is larger
than the values.
You might consider using MATCH or VLOOKUP, as these have an optional
parameter which you can set to TRUE (or 1) (which is the default
setting and works as with LOOKUP) or to FALSE (or 0), and with this
setting you are looking for an exact match and the data does not need
to be sorted. So, an equivalent to your LOOKUP formula would be:
=MATCH(25,F16:F30,0) + 15
MATCH returns the relative position in the array, hence you need to
add 15 onto the result to get the value 25. If you try this with 15 or
35 instead of 25 you will get the #N/A error, and this can be trapped
using ISNA like this:
=IF(ISNA(MATCH(15,F16:F30,0)),"out of range",MATCH(15,F16:F30,0)+15)
which will now give an error message of your choosing - you could make
it "" (i.e. blank), or zero, depending on what you want to do with the
returned value. If you are using XL2007 or later you can use IFERROR
and avoid the repetition of the MATCH function.
Hope this helps, and check out your earlier post if you want me to
have a further look at the transducer problem.
Pete
R
Robert Hatcher
Thanks to you both. I ended up using the match function to get what I
wanted. Which was just indentifing if a value existed in an array and
then returning the name of the array. This was just one part that was
stumpping me so i had simplified it to work it out.
=IF(ISNA(MATCH(B2,INDIRECT(A2),0)),"",A2)
A2 contains the name of the array B2 is the value Im checking for. If
it is in the arry, the name of the array is returned. This work well
but I will look through your recomendatios for a better option.
The next thing for me to sort out is how to look through four arrays
for the one value and return the array name that has the value.
Thanks Again
Robert
wanted. Which was just indentifing if a value existed in an array and
then returning the name of the array. This was just one part that was
stumpping me so i had simplified it to work it out.
=IF(ISNA(MATCH(B2,INDIRECT(A2),0)),"",A2)
A2 contains the name of the array B2 is the value Im checking for. If
it is in the arry, the name of the array is returned. This work well
but I will look through your recomendatios for a better option.
The next thing for me to sort out is how to look through four arrays
for the one value and return the array name that has the value.
Thanks Again
Robert
R
Ron Rosenfeld
The next thing for me to sort out is how to look through four arrays
for the one value and return the array name that has the value.
I'd suggest a different technique.
With your array names listed (as text strings) in A2:A5; and the value in B2, try this formula:
=LOOKUP(2,1/(COUNTIF(INDIRECT($A$2:$A$5),B2)>0),A2:A2)
R
Ron Rosenfeld
Thanks to you both. I ended up using the match function to get what I
wanted. Which was just indentifing if a value existed in an array and
then returning the name of the array. This was just one part that was
stumpping me so i had simplified it to work it out.
=IF(ISNA(MATCH(B2,INDIRECT(A2),0)),"",A2)
A2 contains the name of the array B2 is the value Im checking for. If
it is in the arry, the name of the array is returned. This work well
but I will look through your recomendatios for a better option.
The next thing for me to sort out is how to look through four arrays
for the one value and return the array name that has the value.
Thanks Again
Robert
Typo on my last response. The formula should be:
=LOOKUP(2,1/(COUNTIF(INDIRECT($A$2:$A$5),B2)>0),$A$2:$A$5)
R
Robert Hatcher
That works great Ron. Ive been walking through the formula with the
Formula Evaluator and cannot figure out what the purpose of the
looking up "2" in the vector is. The result of the lookup vector in
one working case is: {#DIV/0!;1;#DIV/0!;#DIV/0!} I dont see how the
"2" is found in there but the formula works. Can you explain how that
works?
Thanks
=LOOKUP(2,1/(COUNTIF(INDIRECT($A$2:$A$5),B2)>0),$A$2:$A$5)-
Formula Evaluator and cannot figure out what the purpose of the
looking up "2" in the vector is. The result of the lookup vector in
one working case is: {#DIV/0!;1;#DIV/0!;#DIV/0!} I dont see how the
"2" is found in there but the formula works. Can you explain how that
works?
Thanks
=LOOKUP(2,1/(COUNTIF(INDIRECT($A$2:$A$5),B2)>0),$A$2:$A$5)-
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.