Vlookup

[Gulf]

"If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is Less than or Equal to lookup_value."

Question:
How to find the minimum value that is greater or equal to lookup_value? For example, in a array 1,2,3,4,5, how can I identify the minimum value, say 3, that is greater than, say 2.5?
Thanks.

[jtp]

You can use the Match function with a match type of -1 however your values need to be in descending order. Ex

=Match(2.5,A1:A5,-1) would = 3 if A1-A5 = 5,4,3,2,1

[Mark@Work]

I agree that the lookup functions often fail to provide options one might think that they aught...

You could try using MATCH() and INDEX()

MATCH searches in much the same way as VLOOKUP(), but returns the position of the match within the range.
You can then use that position to guide INDEX() to get the required data.

The advantage of MATCH() is that if the number it matches is less than the one you want, you can add 1 to the position and get the next number.

It's not as neat as VLOOKUP(), but it is more versatile.

Mark.

[Gulf]

Appreciate your help!