# How to identify column where value drops below 0

I'm trying to figure out a formula to identify in which column the value falls below 0.

I have thousands of rows and dozens of columns, so doing this manually isn't an option

So if my columns and values are as follows:

``````    A   B   C   D
1   Jul Aug Sep Oct
2   25  10  5   -2
3   10  2   -6  -10
``````

So for row 2 I would like to return the result "Oct" (or 4 as the 4th column)as this is the month the value drops below 0. For row 3 I would like to return the value "Sep" (or 3 as the 3rd column).

Any ideas?

• Where are you returning the result to? – Raystafarian Jul 30 '13 at 15:03
• In this simple example it could be to column E, rows 2 and 3 (with row 1 being the column heading) – Dave Barnett Jul 30 '13 at 15:40

It's probably a little simpler to use INDEX/MATCH e.g. this array formula in AV10

`=INDEX(AW\$1:BM\$1,MATCH(TRUE,AW10:BM10<0,0))`

confirmed with CTRL+SHIFT+ENTER

or if you prefer to avoid CSE then add another INDEX function like this and formula can be entered "normally"

`=INDEX(AW\$1:BM\$1,MATCH(TRUE,INDEX(AW10:BM10<0,0),0))`

If there are no numbers < 0 then that will return #N/A error......or you can use IFERROR function to replace with some text, e.g.

`=IFERROR(INDEX(AW\$1:BM\$1,MATCH(TRUE,INDEX(AW10:BM10<0,0),0)),"No negative numbers")`

• Many thanks! that worked perfectly! you're a star! – Dave Barnett Jul 31 '13 at 9:46

You can do this with an array formula. In row 2, select your cell where you want the formula, paste the following into the formula bar, and press Ctrl+Shift+Enter.

``````=INDEX(\$A\$1:\$D\$1,MIN(IF(A2:D2<0,COLUMN(A2:D2))))
``````

For reference, `A1:D1` is the range of column headers (months), and `A2:D2` are the values in the row. What this formula does is look for values less than 0 and then takes the one with smallest column number. It then returns the month header in that column number.

This formula can be filled down to all rows.

EDIT:
If your data does not start at column A, you will have to make a small adjustment to the formula to get it to work. Everything stays the same in the formula except the first argument of the `INDEX` function must be extended to column A. For example, if your data starts in column C, then you would use the following formula instead (entered as an array formula).

``````=INDEX(\$A\$1:\$F\$1,MIN(IF(C2:F2<0,COLUMN(C2:F2))))
``````

The reason for this is that the `INDEX` function's second argument indicates the position in the array, not necessarily the column number. This distinction is obliterated if you just extend the array all the way to column A though (i.e., position in array will equal column number).

• Hi thanks for that... not quite working however. I've entered the formula =INDEX(AW\$1:BM\$1,MIN(IF(AW10:BM10<0,COLUMN(AW10:BM10)))) in cell AV10 as my test data is in the range AW10:BM10..... the titles are in row 1. The first column where the data falls below 0 is BM. When I enter the formula above it returns the error #VALUE!.... any ideas? Thanks again – Dave Barnett Jul 30 '13 at 16:20
• Sorry I hit return before I'd finished my response :-/ – Dave Barnett Jul 30 '13 at 16:24
• Ah ok, the issue is that your data does not start at column A as your example showed. Try this instead: `=INDEX(A\$1:BM\$1,MIN(IF(AW10:BM10<0,COLUMN(AW10:BM10))))`. – Excellll Jul 30 '13 at 16:32