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

  • Thread starter Thread starter nomail1983
  • Start date Start date
N

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.
 
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)
 
Back
Top