Index matching multiple values to target table

Hi All,

I am looking to list/index values from multiple columns and return the column header, I have since edited this with a helper cell. I am unable to use the TEXTJOIN function on excel 2013.
The target table can be reformatted to have 5 individual cells for the corresponding columns.

(edited)

Thanks, ChemistryBMS

"My main issue is the headers are merged cells so may need to create helpers cells to enable this."

You give the answer yourself.


Don't use merged cells, since you get in trouble with it sooner or later.

Ok Thanks for that, but where do I find the input and expected result.

After the reply I will take a look at it.

Please Login or Register  to view this content.

=gethdr(Q4,1)

=gethdr(S4,2)

helpcolumns in AB

=IF(IFERROR(MATCH($P5,INDIRECT("name"&COLUMN()-27),0)>0,"")="","",IF(MATCH($P5,INDIRECT("name"&COLUMN()-27),0)>0,INDIRECT(ADDRESS(4,1+(COLUMN()-28)*3)),"")) and drag across.


in cell Q5

=TEXTJOIN(", ",TRUE,AB5:AF5) and drag down.


See the green cells in the attached file.

@oeldere: from the OP

I am unable to use the TEXTJOIN function on excel 2013.

@John Topley

You're totaly right.

Then he / she should go for your solution.

No helper column:

Please Login or Register  to view this content.

Hi protonLeah,

I have tried to replicate that formula for the adjacent table, replacing P4 for R4 but it is returning the values as blanks then separated by commas. Which part am I missing?

Thanks, ChemistryBMS

I think the problem is the merged cells in A1:N1.
In S4:

Please Login or Register  to view this content.

Seems to work. I also corrected the formula in Q4 that was looking at whole columns before:

Please Login or Register  to view this content.