How to use lookup to retrieve next LARGEST value?

[Jimd]

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)

 

[Pete_UK]

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

 

[Harlan Grove]

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".

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))

 

[Ashish Mathur]

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)

 

[Sige]

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

HTH Sige