software

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

quot;mkbatchquot; wrote:

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

Biff

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