range_lookup in lookup functions

[Guest]

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.

 

[Guest]

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

 

[Biff]

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