Lookup returning values out of range

Robert Hatcher
Guest
Posts: n/a

 29th Sep 2011

=LOOKUP(32,F16:F30)

Robert Hatcher
Guest
Posts: n/a

 29th Sep 2011
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
Thanks
Robert

Pete_UK
Guest
Posts: n/a

 29th Sep 2011
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
> Thanks
> Robert

Robert Hatcher
Guest
Posts: n/a

 29th Sep 2011
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

Robert Hatcher
Guest
Posts: n/a

 29th Sep 2011
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 -

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post RAB Microsoft Excel Programming 1 9th Mar 2009 12:51 AM =?Utf-8?B?aXJ2aW5lNzk=?= Microsoft Excel Worksheet Functions 5 4th Aug 2006 01:33 PM wellsrp Microsoft Excel Discussion 3 10th Oct 2005 05:12 PM Jim Burns Microsoft Excel Worksheet Functions 2 20th Jun 2005 04:04 PM cfincham Microsoft Excel Misc 4 17th Jun 2004 01:07 AM

Features