If cell has characters return column header

anu_gv

Board Regular
Joined
Sep 11, 2015
Messages
62
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.
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
702
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,309
Office Version
  1. 365
Platform
  1. Windows
Check that calculation is set to automatic. On the formula tab, Calculation Options.
 

anu_gv

Board Regular
Joined
Sep 11, 2015
Messages
62
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

Board Regular
Joined
Sep 11, 2015
Messages
62

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,309
Office Version
  1. 365
Platform
  1. Windows
There is nothing wrong with your formula.

+Fluff New.xlsm
ABCDEFGH
1B1C1D1E1F1G1
22B1
33D1
4aC1
5gF1
List
Cell Formulas
RangeFormula
H2:H5H2=INDEX($B$1:$G$1,MATCH(TRUE,INDEX(B2:G2<>"",),0))


Check that your blank cells are truly blank.
 

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
702
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Sep 11, 2015
Messages
62
There is nothing wrong with your formula.

+Fluff New.xlsm
ABCDEFGH
1B1C1D1E1F1G1
22B1
33D1
4aC1
5gF1
List
Cell Formulas
RangeFormula
H2:H5H2=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,309
Office Version
  1. 365
Platform
  1. Windows
What is the formula in B2:G2?
 

anu_gv

Board Regular
Joined
Sep 11, 2015
Messages
62
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,341
Messages
5,601,059
Members
414,426
Latest member
Rutuja kokane

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top