range_lookup in lookup functions

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
 
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
 
Back
Top