| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Function to find 'n'th largest alphanumeric field (like "Large") | Excel Worksheet Functions | |||
| "Type mismatch" when I try to fill an Array variable with "+" | Excel Discussion (Misc queries) | |||
| How do I use "offset" function in "array formula"? | Excel Discussion (Misc queries) | |||
| How to change the default in Excel from "find next" to "find all" | Excel Discussion (Misc queries) | |||
| HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Linear Mode
