Formula to Return Column Heading
 

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

Formula to Return Column Heading
 

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))))

Formula to Return Column Heading
 

=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" wrote in message
oups.com...
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

Formula to Return Column Heading
 

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

Dennis

Formula to Return Column Heading
 

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