How to use lookup to retrieve next LARGEST value?

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

Guest

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 "2.2" 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 "2.5" but lookup returns "2".

Thanks.

Jim
(e-mail address removed)
 
You 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.

Pete
 
Jimd wrote...
....
I want to find the value of "2.2" 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 "2.5" but lookup returns "2".
Click to expand...

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)<>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:B6>B8)*(B3:B6<INT(B8)+1),B3:B6))

If you require any clarifications, please feel free to contact me at
(e-mail address removed)
 
Back
Top