Returning the smallest value above a certain criteria

[Andrea K]

Hi - I have the following array of data, where the top row is the year worked
and the following rows are number of hours worked by employees in that year.
In the last column, I would like to return either a) the first year in which
the employee worked more than 999.99 hours or b) if the first is too
complicated, the number of hours that first exceed 999.99 (and from there
I'll do a look up to figure out the year). In the example, I would like to
return either a) "2001" as the year the employee first worked over 1,000, or
b) 1002 hours (first time that a number over 999.99 appears in the array). I
am stumped, although I suspect the solution isn't that complicated. Thanks
for your suggestions

2000 2001 2002 2003 2004 2005
26 1002 999 1500 1001 990
90 600 800 14 10 1600

 

[ShaneDevenshire]

Hi,

Assuming your data starts in A1 with titles on row 1 then in G2 enter the
formula
=INDEX(A$1:G$1,1,MIN(IF(COLUMN(A2:F2)*(A2:F2>999)>0,COLUMN(A2:F2)*(A2:F2>999),"")))
as an array, that means press Shift+Ctrl+Enter to enter the formula not Enter.

 

[T. Valko]

Try one of these:

Array entered**:

=INDEX(A$1:F$1,MATCH(TRUE,A2:F2>999.99,0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Normally entered:

=INDEX(A$1:F$1,MATCH(TRUE,INDEX(A2:F2>999.99,0),0))

 

[ShaneDevenshire]

Sorry,

change the 999 in my formula to 999.99

 

[muddan madhu]

try this

suppose the hours are in row 2

=LARGE(A2:F2,COUNTIF(A2:F2,">999.99"))