If cell has characters return column header

[anu_gv]

Hello All,

I am trying to find the solution for my problem. Basically, I am looking for cell has character (non blank cell) and populate column header of that cell.
I have tried to use this formula: =INDEX($B$1:$G$1,MATCH(TRUE,INDEX(B2:G2<>"",),0)) in H2 Cell.
Where B1:G1 is the header.
B2:G2 has only one cell has filled value (characters), other cells are blank.
I have more than 200 rows, when I tried to drag the fill handle down to remaining rows all the rows has same cell value has H2, even though the formula is matching with respective index's.
Likewise I have the formula in H3, H4,..... etc., as below:
H3=INDEX($B$1:$G$1,MATCH(TRUE,INDEX(B3:G3<>"",),0))
H4=INDEX($B$1:$G$1,MATCH(TRUE,INDEX(B4:G4<>"",),0))
...etc.,
Not sure what is the problem. Is anyone let me what could be the problem

Thx
Anu.

 

[GraH]

Hi,
Sorry, posted for blanks. You need non-blanks.
But your formula does return the header of the first cell that is filled on the row? That's why I was misguided.

 

[Fluff]

Check that calculation is set to automatic. On the formula tab, Calculation Options.

 

[anu_gv]

Hi,
Sorry, posted for blanks. You need non-blanks.
But your formula does return the header of the first cell that is filled on the row? That's why I was misguided.

Hi GraH,
It brings cell header that has cell with non empty / characters filled in for H3 Cell, but not for H4, H5..... Not sure why?
Is there anything to be corrected in the formula.
BTW, this formula i have searched in the net and tweaked little bit as per my requirement.
Any help is really appreciated.
Best Regards
Anu.

 

[anu_gv]

Check that calculation is set to automatic. On the formula tab, Calculation Options.

Hi Fluff,
Yes, it is set to <Automatic>.
Best Regards
Anu.

 

[Fluff]

There is nothing wrong with your formula.

+Fluff New.xlsm
	A	B	C	D	E	F	G	H
1		B1	C1	D1	E1	F1	G1
2		2						B1
3				3				D1
4			a					C1
5						g		F1
List

Cell Formulas
Range	Formula
H2:H5	H2	=INDEX($B$1:$G$1,MATCH(TRUE,INDEX(B2:G2<>"",),0))

Check that your blank cells are truly blank.

 

[GraH]

Hi GraH,
It brings cell header that has cell with non empty / characters filled in for H3 Cell, but not for H4, H5..... Not sure why?
Is there anything to be corrected in the formula.
BTW, this formula i have searched in the net and tweaked little bit as per my requirement.
Any help is really appreciated.
Best Regards
Anu.

So Anu, you drag this formula to the right? MATCH(TRUE,INDEX(B2:G2<>"",),0) will always return the first position where the condition is true.
Seems you are after something like this
= INDEX($B$1:$G$1,,aggregate(15,6, column($A:$F)/($B2:$G2<>""),column(a$1)))

 

[anu_gv]

There is nothing wrong with your formula.

+Fluff New.xlsm
	A	B	C	D	E	F	G	H
1		B1	C1	D1	E1	F1	G1
2		2						B1
3				3				D1
4			a					C1
5						g		F1
List

Cell Formulas
Range	Formula
H2:H5	H2	=INDEX($B$1:$G$1,MATCH(TRUE,INDEX(B2:G2<>"",),0))

Check that your blank cells are truly blank.

Hi Fluff,

Cells from B2:G2 has expression built in and that brings either <blank> or <characters> to those cells. Since I am pointing to those fields that has value output from expression. Not sure this logic i need to tweak further.
Basically I am trying to get the header of filled cell in the given range. I can also use alternate expression, any feedback is appreciated.


Best Regards
Anu.

 

[Fluff]

What is the formula in B2:G2?

 

[anu_gv]

What is the formula in B2:G2?

I have combination of iferror and vlookup expression that brings either blank or character (Name of individual) depends upon other sheets in the workbook.