Find the column that value becomes zero?

[Ten98]

Hi there,

I have a long list of sites, with predicted expansions over the next 5 years.

I want to show the year that the expansion becomes zero in column H, as per this example.

I'm doing this manually, but there must be some way I can automate this in Excel...

Is there a formula I can enter in column H to return the column heading for when the value of the row becomes zero?

Many thanks,

John.

 

[jim may]

Excel Workbook
	A	B	C	D	E	F	G	H
1		2011	2012	2013	2014	2015	2016	Yr Zero
2	Siate 1	3	2	1	0	0	0	2014
3	Siate 2	2	1	0	0	0	0	2013
4	Siate 3	0	0	0	0	0	0	2011
5	Siate 4	6	5	4	3	2	1	0
Sheet1

Excel 2007

Cell Formulas
Range	Formula
H2	=IF(ISNA(INDEX($B$1:$G$1,1,MATCH(0,$B2:$G2,0))),0,INDEX($B$1:$G$1,1,MATCH(0,$B2:$G2,0)))
H3	=IF(ISNA(INDEX($B$1:$G$1,1,MATCH(0,$B3:$G3,0))),0,INDEX($B$1:$G$1,1,MATCH(0,$B3:$G3,0)))
H4	=IF(ISNA(INDEX($B$1:$G$1,1,MATCH(0,$B4:$G4,0))),0,INDEX($B$1:$G$1,1,MATCH(0,$B4:$G4,0)))
H5	=IF(ISNA(INDEX($B$1:$G$1,1,MATCH(0,$B5:$G5,0))),0,INDEX($B$1:$G$1,1,MATCH(0,$B5:$G5,0)))

 

[Ten98]

Nice one, perfect

 

[gsbelbin]

I hate repeating formula within an IF so my solution was slightly different

=IF(ISNA(MATCH(0,$B2:$G2,0)),0,INDEX($B$1:$G$1,1,MATCH(0,$B2:$G2,0)))

The first part of the IF only looks at the MATCH since that is what generates the error. Slightly more efficient but irrelevant in this situation