Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find first occurence in a list that's greater than a specific num
Is there a way to find the first occurence in a list of unsorted values that
is greater than a specific lookup value? The list is a column of unsorted numbers such that if A1:A5 = 7,4,10,5,9 and the lookup value is 8 it would return 10 (or row 3). VLOOKUP and MATCH don't work because they require the list to be sorted. If there isn't a function that does this, how could I code this in VBA or using a macro Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find first occurence in a list that's greater than a specific num
To return 10, try...
=INDEX(A1:A5,MATCH(TRUE,INDEX(A1:A58,0),0)) To return 3, try... =MATCH(TRUE,INDEX(A1:A58,0),0) Hope this helps! In article , stevep wrote: Is there a way to find the first occurence in a list of unsorted values that is greater than a specific lookup value? The list is a column of unsorted numbers such that if A1:A5 = 7,4,10,5,9 and the lookup value is 8 it would return 10 (or row 3). VLOOKUP and MATCH don't work because they require the list to be sorted. If there isn't a function that does this, how could I code this in VBA or using a macro Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find first occurence in a list that's greater than a specific num
=INDEX(A1:A5,MIN((IF(A1:A58,ROW(A1:A5)))))
which is an array formula, so commit with Ctrl-Shift-Enter -- HTH Bob Phillips (remove nothere from email address if mailing direct) "stevep" wrote in message ... Is there a way to find the first occurence in a list of unsorted values that is greater than a specific lookup value? The list is a column of unsorted numbers such that if A1:A5 = 7,4,10,5,9 and the lookup value is 8 it would return 10 (or row 3). VLOOKUP and MATCH don't work because they require the list to be sorted. If there isn't a function that does this, how could I code this in VBA or using a macro Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to summarize the first 4 numbers greater than 0 from a list. | Excel Worksheet Functions | |||
How do I find a cell starting with a specific letter? | Excel Discussion (Misc queries) | |||
cannot find list in excel 2000 | Excel Discussion (Misc queries) | |||
How Do I Find A Specific Worksheet | Excel Discussion (Misc queries) | |||
How do I find out what items are in one list but not in another l. | Excel Discussion (Misc queries) |