Return value greater than in LOOKUP
 

I am trying to use the lookup function to return the next value greater than
the lookup value in a vector. However, the only thing that the lookup
function will do is return the next value that is less than or equal to. Is
there a way to get it to return the value greater than.

I am using: LOOKUP(25.4,M10:M150,M10:M150)

....and want to return the first value greater than 25.4 in the vector M10:M150

Answer: Return value greater than in LOOKUP
 

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:

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


   ---

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

Return value greater than in LOOKUP
 

return the next value greater than the lookup value

Do you really mean greater than or equal to?

Try this for **greater than** :

=INDEX(M10:M150,MATCH(TRUE,INDEX(M10:M15025.4,0), 0))

Note that if there isn't a value greater than the lookup value you'll get an
error.

--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
I am trying to use the lookup function to return the next value greater
than
the lookup value in a vector. However, the only thing that the lookup
function will do is return the next value that is less than or equal to.
Is
there a way to get it to return the value greater than.

I am using: LOOKUP(25.4,M10:M150,M10:M150)

...and want to return the first value greater than 25.4 in the vector
M10:M150

Return value greater than in LOOKUP
 

Brian,

If your data are sorted you can use

=INDEX(M10:M150,COUNTIF(M10:M150,"<"&24.5)+1)

If it isn't sorted then this ARRAY formula

=INDEX(M10:M150,MATCH(SMALL(M10:M150,COUNTIF(M10:M 150,"<"&24.5)+1),M10:M150,0))


This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Brian" wrote:

I am trying to use the lookup function to return the next value greater than
the lookup value in a vector. However, the only thing that the lookup
function will do is return the next value that is less than or equal to. Is
there a way to get it to return the value greater than.

I am using: LOOKUP(25.4,M10:M150,M10:M150)

...and want to return the first value greater than 25.4 in the vector M10:M150

Return value greater than in LOOKUP
 

Thanks,

That is exactly what I needed. Cuts my work down from several hours to
several minutes.

Brian

"T. Valko" wrote:

return the next value greater than the lookup value

Do you really mean greater than or equal to?

Try this for **greater than** :

=INDEX(M10:M150,MATCH(TRUE,INDEX(M10:M15025.4,0), 0))

Note that if there isn't a value greater than the lookup value you'll get an
error.

--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
I am trying to use the lookup function to return the next value greater
than
the lookup value in a vector. However, the only thing that the lookup
function will do is return the next value that is less than or equal to.
Is
there a way to get it to return the value greater than.

I am using: LOOKUP(25.4,M10:M150,M10:M150)

...and want to return the first value greater than 25.4 in the vector
M10:M150


.