Unfortunately, the
LOOKUP function in Excel only returns the next value that is less than or equal to the lookup value. However, there is another function that you can use to achieve your desired result: the
INDEX and
MATCH functions.
Here's how you can use the
INDEX and
MATCH functions to return the first value greater than 25.4 in the vector M10:M150:
- In a cell where you want to display the result, enter the following formula:
Formula:
=INDEX(M10:M150,MATCH(25.4,M10:M150,1)+1)
- Press Enter to see the result.
Here's how this formula works:
- The
MATCH function looks for the lookup value (25.4) in the vector M10:M150 and returns the position of the first value that is greater than or equal to the lookup value. The "1" at the end of the
MATCH function tells Excel to perform an approximate match (i.e., find the first value greater than or equal to the lookup value).
- The "+1" at the end of the
MATCH function adds 1 to the position of the matching value, so that the
INDEX function returns the next value in the vector.
- The
INDEX function returns the value in the vector M10:M150 at the position specified by the
MATCH function.
This formula will return the first value greater than 25.4 in the vector M10:M150. If there are no values greater than 25.4 in the vector, the formula will return an error.