NEED FORMULA TO PICK UP TOP OF A COLUMN WHEN CERTAIN CRITERIA MET

[z942849]

SPREADSHEET HAS COLUMNS WITH DATA SPREAD OVER 12 MONTHS. IN THE 2ND TO LAST
COLUMN OF DATA, THERE IS TARGET THAT I WANT TO COMPARE TO EACH NUMBER IN THE
SAME ROW. WHERE EVER A NUMBER IS GREATER THAN THE 2ND TO LAST COLUMN, I WANT
FORMULA IN THE LAST COLUMN TO PICK UP THE MONTH FROM THE TOP OF THE COLUMN
WHERE THE NUMBER IS GREATER THAN THE TARGET.

Example:
Jun Jul Aug Sep TARGET Exceeds Plan in Month of:
1,458 1,620 1,781 1,956 1,712 Aug
1,412 1,569 1,726 1,881 1,626 Aug
8 9 9 10 8 Jul
20 22 24 26 19 Jun

 

[T. Valko]

Try this array formula** ...

Column headers in the range A11
E2:E5 = lookup value

Array entered** in F2 and copied down as needed:

=IF(COUNTIF(A22,">"&E2),INDEX(A$1$1,MATCH(TRUE,A22>E2,0)),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[z942849]

meant to respond to this post. Thank you - it worked!

 

[T. Valko]

You're welcome. Thanks for the feedback!