From array find corresponding column/row value

[Guest]

This seems so simple but...
I have an array (zero means blank in this example):
A B C D E
1 0 1 0 0 3
2 1 1 2 5 8
3 0 0 0 0 5
etc
How can I find the first non-blank cell in each row and return the column
heading as the result? e.g.
Row 1 = B
Row 2 = A
Row 3 = E

 

[Dave Peterson]

I put this in F2:
=IF(COUNTA(A2:E2)=0,"none!",INDEX($A$1:$E$1,MATCH(TRUE,A2:E2<>"",0)))
And dragged down the column.

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

 

[Guest]

assuming your data starts from second row and your titles are in first row:

=INDEX($A$1:$E$1,1,MATCH(SMALL($A2:$E2,COUNTIF($A2:$E2,0)+1),$A2:$E2,0))

 

[Guest]

Thanks Dave
I was working the index/match route but could not get the synatax right. Is
there a super Excel class where one can learn these tricks? I can't take much
more of the help files--I spent an hour before caving in and asking the
discussion group.

 

[Dave Peterson]

Hang around in the .worksheet.functions newsgroup and you'll pick up lots of
stuff.

It's scary how much those people can do.