match function?
 

Why doesn't the match function work if the list is out-of-order?

Is there a function that will find the FIRST VALUE IN A LIST (EVEN IF
IT IS OUT-OF-ORDER) that is the smallest value greater than or equal
to the specified value?

For example, given in cells A1:A4:

2
3
5
1

How do I ask for the first value that comes along that is greater than
or equal to 4? So that the return value would be "3" since 5 is in
the "3"-position of the list.

match function?
 

Here you go

=MATCH(SMALL(A1:A20,COUNTIF(A1:A20,"<"&D1)+1),A1:A 20,0)


with 4 in D1


hardcoded


=MATCH(SMALL(A1:A20,COUNTIF(A1:A20,"<4")+1),A1:A20 ,0)



--


Regards,


Peo Sjoblom


--


Regards,


Peo Sjoblom

"Matt" wrote in message
...
Why doesn't the match function work if the list is out-of-order?

Is there a function that will find the FIRST VALUE IN A LIST (EVEN IF
IT IS OUT-OF-ORDER) that is the smallest value greater than or equal
to the specified value?

For example, given in cells A1:A4:

2
3
5
1

How do I ask for the first value that comes along that is greater than
or equal to 4? So that the return value would be "3" since 5 is in
the "3"-position of the list.