From array find corresponding column/row value
 

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

From array find corresponding column/row value
 

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

Steve-in-austin wrote:

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

From array find corresponding column/row value
 

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

"Steve-in-austin" wrote:

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

From array find corresponding column/row value
 

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" wrote:

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

Steve-in-austin wrote:

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

From array find corresponding column/row value
 

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

It's scary how much those people can do.

Steve-in-austin wrote:

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" wrote:

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

Steve-in-austin wrote:

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


--

Dave Peterson