Does anyone know how to write a formula that will lookup a value and
return the next largest value?

For an example:

A B
1 100 120
2 200
3 300
4 400


I know the formula to return the value that's less than the value I am
looking up.
This is what I use =VLOOKUP(B1, A2:A4, 1, TRUE)
returns 100

How do I get it to return 200? Since 120 is between 100 and 200.

Any help is greatly appreciated!

Sort your data in A descending (400,300,200,100).
Then use this formula:

=INDEX(A1:A4,MATCH(B1,A1:A4,-1))


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

wrote in message oups.com...
| Does anyone know how to write a formula that will lookup a value and
| return the next largest value?
|
| For an example:
|
| A B
| 1 100 120
| 2 200
| 3 300
| 4 400
|
|
| I know the formula to return the value that's less than the value I am
| looking up.
| This is what I use =VLOOKUP(B1, A2:A4, 1, TRUE)
| returns 100
|
| How do I get it to return 200? Since 120 is between 100 and 200.
|
| Any help is greatly appreciated!
|

thank you, that worked!


Niek Otten wrote:
Sort your data in A descending (400,300,200,100).
Then use this formula:

=INDEX(A1:A4,MATCH(B1,A1:A4,-1))


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

wrote in message oups.com...
| Does anyone know how to write a formula that will lookup a value and
| return the next largest value?
|
| For an example:
|
| A B
| 1 100 120
| 2 200
| 3 300
| 4 400
|
|
| I know the formula to return the value that's less than the value I am
| looking up.
| This is what I use =VLOOKUP(B1, A2:A4, 1, TRUE)
| returns 100
|
| How do I get it to return 200? Since 120 is between 100 and 200.
|
| Any help is greatly appreciated!
|