software

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(c2lt;gt;0,c1,(if(d2lt;gt;0,d1),(if(e2lt;gt;0,e1)
,(if(f2lt;gt;0,f1),(if(g2lt;gt;0,g1) but it did not work.

I hope this makes sense, and any help will surely be appreciated.DennisWhere C1:G1 are your headers and data starting in row 2, the formula in
B2 is
=IF(C2lt;gt;0,C$1,IF(D2lt;gt;0,D$1,IF(E2lt;gt;0,E$1,IF(F2lt;gt;0,F $1,G$1))))=INDEX($B$1:$F$1,MATCH(TRUE,($B2:$F2lt;gt;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)

quot;den4673quot; gt; wrote in message oups.com...
gt; I have a worksheet that has 5 columns and approximately 20,000 rows.
gt; Only one cell in each row has a value other that zero. I want to
gt; insert a column on the left that will have a formula that will look at
gt; each value in each row and return the column heading for the column
gt; that has the row containing a value other than zero. I have tried a
gt; formula that says =if(c2lt;gt;0,c1,(if(d2lt;gt;0,d1),(if(e2lt;gt;0,e1)
gt; ,(if(f2lt;gt;0,f1),(if(g2lt;gt;0,g1) but it did not work.
gt;
gt; I hope this makes sense, and any help will surely be appreciated.
gt;
gt;
gt; Dennis
gt;
Thank you very much. I see from your example what I was doing wrong.

DennisThanks 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