=LOOKUP(32,F16:F30)

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

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

On Sep 29, 3:42*pm, Robert Hatcher <rhatcher...@gmail.com> wrote:
> 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

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

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)-

On Sep 29, 12:06*pm, Ron Rosenfeld <r...@nospam.net> wrote:
> On Thu, 29 Sep 2011 08:44:03 -0700 (PDT), Robert Hatcher <rhatcher...@gmail.com> wrote:
> >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)- Hide quotedtext -
>
> - Show quoted text -