VLOOKUP and searching for largest value

Hi,

I have the following column of values

10
20
30
40
50

Now, I need to look for the value 35 (lookup value) in that column of values, and excel needs to return me the next largest value, i.e. 40. With VLOOKUP I can only make it work for the next smallest value, i.e. 30. I tried using INDEX and MATCH combined but it seems that they only work with two full arrays. I basically only have 1 array and one single lookup value. I dont have space to build additional columns with inverted values etc. How can I elegantly perform the function of finding the largest value in a single column based on a single lookup value?

Lucas

Originally Posted by Richard Buttrey

Hi, and welcome to the forum

One way would be

Formula:

Please Login or Register  to view this content.

This assumes of course that the values are sorted in ascending order.

That one did it for me!! :-) Thanks a lot.

Lucas