Find first occurence in a list that's greater than a specific num

G

Guest

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!
 
D

Domenic

To return 10, try...

=INDEX(A1:A5,MATCH(TRUE,INDEX(A1:A5>8,0),0))

To return 3, try...

=MATCH(TRUE,INDEX(A1:A5>8,0),0)

Hope this helps!
 
B

Bob Phillips

=INDEX(A1:A5,MIN((IF(A1:A5>8,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)
 

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

Location of the first greater than cell 3
First occurance greater than or equal to a specified value 3
vlookup and match first value greater than 4
first cell in a column with a specific value 3
vlookup - find an alternative approximate match 4
Vlookup match greater than lookup value 4
Find first occurence of a number in an array 7 cols wide 3
lookup number greater than 2

Top