If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
lookup number greater than
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) |
#2
|
|||
|
|||
lookup number greater than
For the column search set-up you have, try this array-entered** formula...
=INDEX(A1:A100,MATCH(C1+MIN(IF(B1:B100C1,"",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 news 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) |
#3
|
|||
|
|||
lookup number greater than
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 news 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) |
#4
|
|||
|
|||
lookup number greater than
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) |
Thread Tools | |
Display Modes | |
|
|