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

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.

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

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)

--
Biff
Microsoft Excel MVP


wrote in message
ups.com...
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.

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

On Sep 21, 11:44 pm, "T. Valko" wrote:
wrote in message
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?

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

Perfect! Thanks.

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

wrote in message
ups.com...
On Sep 21, 11:44 pm, "T. Valko" wrote:
wrote in message
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?

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

Perfect! Thanks.


You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP