| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Less Than MATCH()
Is there any way to use the MATCH() function to grab only values that
are less than the lookup value (instead of <=)? I've considered using an IF() with ISNA(MATCH(**,**,0)) to setup separate conditions, but I'm afraid of what it will to do my already long processing time. Any help would be appreciated. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Less Than MATCH()
Try this:
=MATCH(**,**,1) " wrote: Is there any way to use the MATCH() function to grab only values that are less than the lookup value (instead of <=)? I've considered using an IF() with ISNA(MATCH(**,**,0)) to setup separate conditions, but I'm afraid of what it will to do my already long processing time. Any help would be appreciated. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Less Than MATCH()
Make sure your data is sorted in Ascending order
" wrote: Is there any way to use the MATCH() function to grab only values that are less than the lookup value (instead of <=)? I've considered using an IF() with ISNA(MATCH(**,**,0)) to setup separate conditions, but I'm afraid of what it will to do my already long processing time. Any help would be appreciated. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Less Than MATCH()
MATCH(**,**,1) returns the largest value that is less than **or equal
to** the lookup value. I'm trying to find the closest value that is only less than my lookup. "-1" as the final parameter would probably work instead, but I can't sort my data in descending order. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Less Than MATCH()
How about subtracting one from the lookup value?
e.g =MATCH(I2-1,G3:G12) assuming that the lookp array is sorted Peter " wrote: Is there any way to use the MATCH() function to grab only values that are less than the lookup value (instead of <=)? I've considered using an IF() with ISNA(MATCH(**,**,0)) to setup separate conditions, but I'm afraid of what it will to do my already long processing time. Any help would be appreciated. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Less Than MATCH()
=SUMPRODUCT( MAX( (A1:A10<YourValue)*A1:A10) )
- David Hilberg On Jul 20, 2:43 pm, wrote: Is there any way to use the MATCH() function to grab only values that are less than the lookup value (instead of <=)? I've considered using an IF() with ISNA(MATCH(**,**,0)) to setup separate conditions, but I'm afraid of what it will to do my already long processing time. Any help would be appreciated. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Less Than MATCH()
Thanks for the help. I think I've got it figured out now.
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
| Lookup? Match? pulling rows from one spreadsheet to match a text f | Excel Worksheet Functions | |||
| Any way for 2 column vlookups. i.e match last name then match firs | Excel Worksheet Functions | |||
| index,match,match on un-sorted data | Excel Worksheet Functions | |||
| When MATCH and v/hLOOKUP functions *FAIL* to match (but they should)... | Excel Worksheet Functions | |||
Linear Mode
