lookup number greater than

A

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)
 
R

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
 
M

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top