Show the column header to a row if there is value

Question

Update 1:

Because my question is not clear, so I post second example

Because row 2, there is value 1 at Item 04 so the Get Item Name = Item 04. It is random and have a large number of columns (500).

The problem:

I would like to have a way to get a column header if there is any value input to the cells under that header. Please note that if at row 2 and column 1 has value, then other cell of row 2 will not have any value (other than 0).

It is hard to explain the problem in words so I have created an example.

Answer 1

I posted a formula in the comments above, you may not have seen it. This is it:

=IF(COUNTA($B2:$D2)=0,"",INDEX($B$1:$D$1,MATCH(TRUE,INDEX($B2:$D2<>"",0),0)))

that will get the header for the first instance of a populated cell - if you have numeric values and want to ignore zeroes change to

=IF(COUNTA($B2:$D2)=0,"",INDEX($B$1:$D$1,MATCH(TRUE,INDEX($B2:$D2>0,0),0)))

Either way the formula can be extended to as large a range as you need

.....and if you have 500 columns you could use IFERROR to shorten a little

=IFERROR(INDEX($B$1:$D$1,MATCH(TRUE,INDEX($B2:$D2>0,0),0)),"")

Answer 2

I assume that when you say "column header" you really mean "row header".

Considering that the first cell (containing the text "Column Header") is at A1 this is the formula you have to introduce in cell A2:

=IF(B2>0, $B$1, IF(C2>0, $C$1, IF(D2>0, $D$1, "")))

Drag this formula down on as many rows as you need and it'll (hopefully) achieve what you want.

Answer 3

Assuming that you want a solution with more than 3 columns, this formula will return you the right header.

I assume that you data starts in row 2, column B - and row 1 contains the column headers.

Use this formula in B1 and copy it down:

=IF(COUNTA($B2:$D2)=0,"",INDEX($B$1:$D$1,MATCH(FALSE,INDEX(ISBLANK($B2:$D2),0),0)))

If your data extends further than column D, simply change this in the formula.

Answer 4

for a particular table which is relative small table with countable number of columns (i.e 3) try this

=IF(COUNTA(G8)>0,"Column 1",IF(COUNTA(H8)>0,"Column 2",IF(COUNTA(I8)>0,"Column 3",""))).  

my question is what if you have table contain 20 or 100 columns?