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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,071
Office Version
  1. 365
Platform
  1. Windows
Do those formulae return "" or maybe " " if there is an error?
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

anu_gv

Board Regular
Joined
Sep 11, 2015
Messages
62
Do those formulae return "" or maybe " " if there is an error?

Hi Fluff,

Here is the expression I used on those cells from B2:G2.
=IFERROR(VLOOKUP(A2,SheetRef!A:A,1,FALSE),"")
Where SheetRef has 6 different sheets.

Best Regards
Anu.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,071
Office Version
  1. 365
Platform
  1. Windows
In that case that shouldn't be a problem.
As you drag down the formula in your op, it should list the header for first value in each row.

Can you post an example of your data showing the problem, using the XL2BB add-in.
 

anu_gv

Board Regular
Joined
Sep 11, 2015
Messages
62
In that case that shouldn't be a problem.
As you drag down the formula in your op, it should list the header for first value in each row.

Can you post an example of your data showing the problem, using the XL2BB add-in.
Hi Fluff,
I am not getting any error.
I am getting incorrect values populated in H3, H4, H5, etc., such as where the empty cells column header instead of cell header with characters filled.
Further more, I have tried the same formula in my personal computer and formula works as I expected. Except the cell range from B2:G2 do not have any expression mentioned.
I have tried to use other expression, but I don't find any better solution.

Thx
Anu.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,071
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

When you tried the formula on your personal computer, where you using the same data, as the one that is not working correctly?
 

anu_gv

Board Regular
Joined
Sep 11, 2015
Messages
62
When you tried the formula on your personal computer, where you using the same data, as the one that is not working correctly?
No. Fluff.
I have mimic'd same type of concept and manually created those values.
Best Regards
Vasanth.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,071
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

In that case the problem lies with the original data. Make sure that none of the formulae are returning a space " " rather than "".
Also on your original data is the sheet set to hide 0 values?
 

anu_gv

Board Regular
Joined
Sep 11, 2015
Messages
62
In that case the problem lies with the original data. Make sure that none of the formulae are returning a space " " rather than "".
Also on your original data is the sheet set to hide 0 values?
You are correct Fluff. Column C has space, that is where the problem started.
I have corrected it. It worked fine.
Thanks a lot for guiding me on this problem.
Best Regards
Anu.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,071
Office Version
  1. 365
Platform
  1. Windows
Glad it's sorted & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,122,850
Messages
5,598,455
Members
414,239
Latest member
xnanx

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