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

Z

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

T. Valko

Try this array formula** ...

Column headers in the range A1:D1
E2:E5 = lookup value

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

=IF(COUNTIF(A2:D2,">"&E2),INDEX(A$1:D$1,MATCH(TRUE,A2:D2>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.
 

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

Top