lookup number greater than

[Ade Taiwo]

I want to Look in a one-row or one-column range for a value and return a
value from the same position in a second one-row or one-column range, but
instead of returning the largest value in lookup_vector that is less than or
equal to lookup_value I want to return the largest value in lookup_vector
that is greater than or equal to the lookup_value.
Example,
lookup_value is 53
Col1 Col2
Row1 16 50
Row2 25 63

I want a formula that can return 25 instead of 16(using lookup function)

 

[Rick Rothstein]

For the column search set-up you have, try this array-entered** formula...

=INDEX(A1:A100,MATCH(C1+MIN(IF(B1:B100<C1,"",B1:B100-C1)),B1:B100,0))

I assumed for this formula, that C1 contains the "look_up value". Change the
all the upper row limits (the 100s) in the formula to a row number that will
be larger than largest expected row which might ever have data in it.

**Commit the formula using Ctrl+Shift+Enter and not Enter by itself

 

[Mike H]

Maybe this array formula with the lookup value in C1

=MIN(IF(B1:B100>=C1,A1:A100))

This is an array formula which must be entered with 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 ranges
'then you must re-enter as An array

Mike