Sample Data Sheet: https://docs.google.com/spreadsheets/d/1EIQcpzwdaoNSKEPu-F1NCUOtEjS6Ez0P73dmotClT7Q/edit?usp=sharing
The end goal is to have a Month + Year show up in a single cell, based on the first instance of "New Total" being >=0.
The sheet includes sample data, and the formatting we are interacting with.
In the purple box, I want the month + year to populate, based on the first instance of "New Total" being >=0. When the formula reaches the first instance of >=0 (March in the sample data), I want it to then pull "March" from cell Q2 and concatenate with "2019" from cell "S2". The values of "New Total" are dynamic, and will change periodically. Right now, the sample data should result in "March 2019", but tomorrow it might change so that "February" is the first >=0 instance.
Simplified:
Input: Row of Numbers, looking for the first instance of "New Total" being >=0.
Output: Pull Month + Year once "New Total" has found int >=0 in the specified row. Display in the specified cell.
Here's how it was done with the nested IF statements:
=IF(G15>=0,E14&" "&G14,IF(J15>=0,H14&" "&J14,IF(M15>=0,K14&" "&M14,
Best Answer
Ryan, I feel obliged to say that this isn't an ideal data setup. That said, if you're committed to keeping it, you can accomplish your goal with a version of the following (which I have placed in your second vertical block reading "January 2018":
=INDEX(14:14,MATCH(1,(ISNUMBER(15:15)*(15:15>0)),0)-2)&" "&INDEX(14:14,MATCH(1,(ISNUMBER(15:15)*(15:15>0)),0))To use it elsewhere, just change the row range 14:14 to match your upper text row with the months and years, and change 15:15 to match the row where your currency amounts are.
HOW IT WORKS
First, spot the &" "& in the middle. Everything before this results in the month name. Everything after it results in the year. The ampersand(&) means "stick these together." So we will get monthname+onespace+year in the end.
The portion of the formula that retrieves the month and the portion that retrieves the year work similarly:
INDEX(14:14,_____)In English, "Look across row 14."
MATCH(1,_____,0)We are going to create a virtual row of true or false values that match up with every cell in row 14. In programming, a 1 means TRUE (or "Yes" or "I found it") and a 0 means FALSE (or "No" or "I didn't find it").
The zero at the end of the match means we want an exact match. It answers the question "Will you accept something close?" to which our answer is FALSE / NO (or, as I explained, 0 for that part).
ISNUMBER(15:15)*(15:15>0)We are trying to test two conditions here in order to find our MATCH:
First condition:
ISNUMBER(15:15)This tests every cell in row 15 to see if it is a number. Those cells that contain a number will earn a 1 (for TRUE/YES). Those cells that do not contain a number will get a 0 (FALSE/NO).Second condition:
(15:15>0)This is a second test on every cell in row 15 to see if it is contains a value greater than zero. If it does, that cell gets a 1; if not, it gets a 0.The asterisk multiplies our first answer by our second answer. So for instance, if the first cell in row 15 held text, it would get a score of 0 (FALSE) for "Is it a number?" and 0 (FALSE) for "Is it greater than zero?" The cumulative score, then for that cell would be 0 * 0 or ... just 0.
As the formula looks across row 15, if it finds a negative number, the test returns 1 (TRUE) for "Is it a number?" and 0 (FALSE) for "Is it greater than zero?" The cumulative score for this cell will still be 0, because 1 * 0 = 0.
So the only cells that will get a 1 will be those that both contain a number and are greater than zero (which is what you're looking for).
In the end, let's say that row 15 had 25 cells in it. The virtual "array" (i.e., list) that
ISNUMBER(15:15)*(15:15>0)creates will look something like this:{0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,1,0,0,0,0}
Remember, the MATCH function will be looking for the first instance of 1:
MATCH(1,(ISNUMBER(15:15)*(15:15>0)),0)When it finds it, we know which column the first match is in.
Back to INDEX:
INDEX(14:14,MATCH(1,(ISNUMBER(15:15)*(15:15>0)),0))INDEX will return the value in row 14 that matches the spot where MATCH found TRUE*TRUE (or 1). So we would now wind up with the YEAR from row 14 that's above the first numeric value greater than 0 in row 15.
But we want that second (which is why you'll see this exact formula portion after the &" "&).
In order to get the month, we just back up two cells from the year. So the first part of the formula, before &" "&, adds a -2:
INDEX(14:14,MATCH(1,(ISNUMBER(15:15)*(15:15>0)),0)-2)In other words, look across row 14 until you find the TRUE/TRUE match in row 15, then back up 2 from where you find it.
In the end, we get:
=INDEX(14:14,MATCH(1,(ISNUMBER(15:15)*(15:15>0)),0)-2)(month name)&" "&(and a single space and)INDEX(14:14,MATCH(1,(ISNUMBER(15:15)*(15:15>0)),0))(year)