Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
stevep
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Domenic
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to summarize the first 4 numbers greater than 0 from a list. Antonio Excel Worksheet Functions 7 November 25th 05 12:34 AM
How do I find a cell starting with a specific letter? Bking Excel Discussion (Misc queries) 5 July 18th 05 05:14 AM
cannot find list in excel 2000 tanu Excel Discussion (Misc queries) 3 April 27th 05 11:44 PM
How Do I Find A Specific Worksheet Chris N Excel Discussion (Misc queries) 1 December 27th 04 07:53 PM
How do I find out what items are in one list but not in another l. Michelle Craig Excel Discussion (Misc queries) 2 December 22nd 04 09:32 PM


All times are GMT +1. The time now is 02:26 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"