is there any way to have excel return the next largest value that is MORE
than the 'lookup_value' when 'range_lookup' = 'true'? (it normally defaults
to the next largest value that is LESS)
or is there another way around this problem? eg - my lookup_value is 105;
my table array may or may noy have 105, but always has numbers higher than
105 - the desired value to return is for the next number availble after 105.

regards.

You can sort your list in descending order and use the MATCH() function with
the third argument of -1. You'll probably have to use the MATCH() within an
INDEX() function

"mkbatch" wrote:

is there any way to have excel return the next largest value that is MORE
than the 'lookup_value' when 'range_lookup' = 'true'? (it normally defaults
to the next largest value that is LESS)
or is there another way around this problem? eg - my lookup_value is 105;
my table array may or may noy have 105, but always has numbers higher than
105 - the desired value to return is for the next number availble after 105.

regards.

If your table is already sorted ascending and you can't or don't want to
sort your table descending:

Array entered using the key combo of CTRL,SHIFT,ENTER:

A1 = lookup value

=INDEX(C1:C10,MATCH(TRUE,B1:B10=A1,0))

Biff

"mkbatch" wrote in message
...
is there any way to have excel return the next largest value that is MORE
than the 'lookup_value' when 'range_lookup' = 'true'? (it normally
defaults
to the next largest value that is LESS)
or is there another way around this problem? eg - my lookup_value is 105;
my table array may or may noy have 105, but always has numbers higher than
105 - the desired value to return is for the next number availble after
105.

regards.