Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Next largest value that is greater than lookup_value.
How do I find next largest value that is greater than lookup_value using the
lookup function or any other functions? |
#2
|
|||
|
|||
One way
=INDEX(A1:A10,MATCH(SMALL(A1:A10,COUNTIF(A1:A10,"< "&C1)+1),A1:A10,0)) where A1:A10 is the lookup range and C1 the lookup value -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "bp" wrote in message ... How do I find next largest value that is greater than lookup_value using the lookup function or any other functions? |
#3
|
|||
|
|||
One way:
=MIN(IF(A1:A10C1,A1:A10)) Array-entered, meaning press ctrl/shift/enter. Your range to evaluate is A1:A10 and C1 holds the lookup value. HTH Jason Atlanta, GA -----Original Message----- How do I find next largest value that is greater than lookup_value using the lookup function or any other functions? . |
#4
|
|||
|
|||
Hi Peo. I think you mean <= , not < .
Jason -----Original Message----- One way =INDEX(A1:A10,MATCH(SMALL(A1:A10,COUNTIF(A1:A10," <"&C1) +1),A1:A10,0)) where A1:A10 is the lookup range and C1 the lookup value -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "bp" wrote in message ... How do I find next largest value that is greater than lookup_value using the lookup function or any other functions? . |
#5
|
|||
|
|||
Hi Jason,
it depends on if the OP really wants the next larger value even if it is an exact match, I wasn't sure about that -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Jason Morin" wrote in message ... Hi Peo. I think you mean <= , not < . Jason -----Original Message----- One way =INDEX(A1:A10,MATCH(SMALL(A1:A10,COUNTIF(A1:A10, "<"&C1) +1),A1:A10,0)) where A1:A10 is the lookup range and C1 the lookup value -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "bp" wrote in message ... How do I find next largest value that is greater than lookup_value using the lookup function or any other functions? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ranking cells largest to smallest | Excel Discussion (Misc queries) | |||
Addition to Turn cell red if today is greater or equal to date in cell | New Users to Excel | |||
Turn cell red if today is greater or equal to date in cell | New Users to Excel | |||
input value in a cell when the previous cell is greater than 0 | New Users to Excel | |||
Linking items GREATER THAN O on another worksheet in the same Work | Excel Discussion (Misc queries) |