How to find largest value "<=" when array is in descending order?

[nomail1983]

MATCH(...,1) returns the position of the largest value less than or
equal to the lookup value when the lookup array is sorted in asceding
order.

Is there a standard Excel function or standard Excel add-in that
returns the position of the largest value less than or equal to the
lookup value when the lookup array is sorted in descending order?

I know I can write a VBA function. And there might be non-standard
add-ins -- that is, add-ins that are not distributed with Excel. But
I prefer to use a standard Excel function or standard Excel add-in.

 

[T. Valko]

Try this array formula** :

=MATCH(MAX(IF(A1:A10<=C1,A1:A10)),A1:A10,0)

Where C1 = lookup value

If the lookup value is less than the minimum value in the lookup array the
formula will return #N/A.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[nomail1983]

Try this array formula** :
=MATCH(MAX(IF(A1:A10<=C1,A1:A10)),A1:A10,0)

Perfect! Thanks.

 

[T. Valko]

Perfect! Thanks.

You're welcome. Thanks for the feedback!