software

Using 'lookup' or 'vlookup' I can determine an exact match value or the next
SMALLEST value, but not the next LARGEST value. How to I return the next
largest value?

ex.

I want to find the value of quot;2.2quot; or the next greater value in a table and
my table looks like this:

1
1.5
2
2.5
3

The answer I'm looking for would be quot;2.5quot; but lookup returns quot;2quot;.

Thanks.

JimYou can use the MATCH( ) function to look for either an exact match, or
the next smallest (data needs to be sorted in ascending order) OR the
next largest (data needs to be sorted in descending order). This is
controlled by the match_type parameter (the third parameter) being set
to -1.

You can use INDEX( MATCH( ) ) in a similar way as VLOOKUP(
).

Hope this helps.

PeteJimd wrote...
....
gt;I want to find the value of quot;2.2quot; or the next greater value in a table and
gt;my table looks like this:
gt;
gt;1
gt;1.5
gt;2
gt;2.5
gt;3
gt;
gt;The answer I'm looking for would be quot;2.5quot; but lookup returns quot;2quot;.

Assuming you don't want to resort this list, which I'll assume is named
LST, and your value sought, 2.2, is in a cell named v, try this
formula.

=INDEX(LST,MATCH(v,LST) (VLOOKUP(v,LST,1)lt;gt;v))Hi,

Try this.

The following data is in range B3:B6

1
2
3
2.5

In cell B8, type 2.2. In cell E8 or wherever, enter te following array
formula (Ctrl Shift Enter)

=MAX(IF((B3:B6gt;B8)*(B3:B6lt;INT(B8) 1),B3:B6))

If you require any clarifications, please feel free to contact me at
quot;Jimdquot; wrote:

gt; Using 'lookup' or 'vlookup' I can determine an exact match value or the next
gt; SMALLEST value, but not the next LARGEST value. How to I return the next
gt; largest value?
gt;
gt; ex.
gt;
gt; I want to find the value of quot;2.2quot; or the next greater value in a table and
gt; my table looks like this:
gt;
gt; 1
gt; 1.5
gt; 2
gt; 2.5
gt; 3
gt;
gt; The answer I'm looking for would be quot;2.5quot; but lookup returns quot;2quot;.
gt;
gt; Thanks.
gt;
gt; Jim
gt;

Hi ,
Are you not looking just for: =LARGE(list;2)
?

HTH SigeAshish Mathur wrote:
gt; Hi,
gt;
gt; Try this.
gt;
gt; The following data is in range B3:B6
gt;
gt; 1
gt; 2
gt; 3
gt; 2.5
gt;
gt; In cell B8, type 2.2. In cell E8 or wherever, enter te following array
gt; formula (Ctrl Shift Enter)
gt;
gt; =MAX(IF((B3:B6gt;B8)*(B3:B6lt;INT(B8) 1),B3:B6))
gt;
gt; If you require any clarifications, please feel free to contact me at
gt;
gt;
gt;
gt; quot;Jimdquot; wrote:
gt;
gt; gt; Using 'lookup' or 'vlookup' I can determine an exact match value or the next
gt; gt; SMALLEST value, but not the next LARGEST value. How to I return the next
gt; gt; largest value?
gt; gt;
gt; gt; ex.
gt; gt;
gt; gt; I want to find the value of quot;2.2quot; or the next greater value in a table and
gt; gt; my table looks like this:
gt; gt;
gt; gt; 1
gt; gt; 1.5
gt; gt; 2
gt; gt; 2.5
gt; gt; 3
gt; gt;
gt; gt; The answer I'm looking for would be quot;2.5quot; but lookup returns quot;2quot;.
gt; gt;
gt; gt; Thanks.
gt; gt;
gt; gt; Jim
gt; gt;