First occurance greater than or equal to a specified value

[dukelandshark]

Hey-

I have a list of unsorted data and I want to proceed from the top of a
known column to the bottom and return the first number that is greater
than equal to a specified value.

DMIN doesn't work because I want the first occurance that is greater
than the specified value, not necessarily the smallest number.

Thanks.
-Rob

 

[Peo Sjoblom]

Do you really mean first value greater than or equal that means if you want
to find 12.5 and the second value is 1000
then it will return 1000? Or do you mean find the values that is equal to or
closest larger value? If the latter use

=INDEX(A1:A30,MATCH(SMALL(A1:A30,COUNTIF(A1:A30,"<"&D1)+1),A1:A30,0))

where A1:A30 is the range you want to lookup and D1 holds the lookup value


if 12.5 does not exist it will find the closest larger value for example 13
and not 12.3
If the former

=INDEX(A1:A30,MATCH(TRUE,A1:A30>=D1,0))

entered with ctrl + shift & enter

will find the first value that is greater than or equal to 12.5



--
Regards,

Peo Sjoblom

Portland, Oregon

 

[Domenic]

Assuming that A1:A10 contains your data, and B1 contains the 'specified
value', try...

=INDEX(A1:A10,MATCH(TRUE,A1:A10>=B1,0))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[dukelandshark]

That worked! Thanks both of you!

-Rob

Assuming that A1:A10 contains your data, and B1 contains the 'specified
value', try...

=INDEX(A1:A10,MATCH(TRUE,A1:A10>=B1,0))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!