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)

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

=INDEX(A1:A100,MATCH(C1+MIN(IF(B1:B100<C1,"",B1:B1 00-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

--
Rick (MVP - Excel)


"Ade Taiwo" wrote in message
...
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)

Non-array formula:

Enter lookup value (53) in C1, then try:

=INDEX(A1:A2,MATCH(SMALL(B1:B2,COUNTIF(B1:B2,"<"&C 1)+1),B1:B2,0))
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Ade Taiwo" wrote in message
...
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)

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

"Ade Taiwo" wrote:

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)