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

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.
 
T

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

lookup number greater than 2
How do I search for the second largest value in the array? 1
Array-friendly functions 1
Array to find relative position - is there a better way? 3
Help w/ LOOKUP function!! 7
Match function 1
Reference to the N-largest value in an array 4
How to use lookup to retrieve next LARGEST value? 4

Top