Formula to Return Column Heading

[den4673]

I have a worksheet that has 5 columns and approximately 20,000 rows.
Only one cell in each row has a value other that zero. I want to
insert a column on the left that will have a formula that will look at
each value in each row and return the column heading for the column
that has the row containing a value other than zero. I have tried a
formula that says =if(c2<>0,c1,(if(d2<>0,d1),(if(e2<>0,e1)
,(if(f2<>0,f1),(if(g2<>0,g1) but it did not work.

I hope this makes sense, and any help will surely be appreciated.


Dennis

 

[Dave O]

Where C1:G1 are your headers and data starting in row 2, the formula in
B2 is
=IF(C2<>0,C$1,IF(D2<>0,D$1,IF(E2<>0,E$1,IF(F2<>0,F$1,G$1))))

 

[Bob Phillips]

=INDEX($B$1:$F$1,MATCH(TRUE,($B2:$F2<>0),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter, and copy down each row

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[den4673]

Thank you very much. I see from your example what I was doing wrong.

Dennis

 

[den4673]

Thanks for the help. I have never used the Index function before and
am having a bit of trouble getting it to work here. I am not sure what
I am doing wrong but will continue until if get it down right.

Dennis