G Guest Dec 2, 2004 #1 How do I find next largest value that is greater than lookup_value using the lookup function or any other functions?
How do I find next largest value that is greater than lookup_value using the lookup function or any other functions?
P Peo Sjoblom Dec 2, 2004 #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)
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)
J Jason Morin Dec 2, 2004 #3 One way: =MIN(IF(A1:A10>C1,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
One way: =MIN(IF(A1:A10>C1,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
J Jason Morin Dec 2, 2004 #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) . Click to expand...
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) . Click to expand...
P Peo Sjoblom Dec 2, 2004 #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)
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)
