Lookup Help - Scan Grid of data and if a value exist return the header - Excel 2016

[Johnny Thunder]

Hello all,

I am working on a project and I have come to a problem I don't know how to solve,

I have a grid of Data, Row Descriptions (Column B), and then My headers in columns "H:AP" and then the corresponding rows in between Range("H9:AP128").

So, I need code or even a formula that will go row by row, Example( Scan Row 9, if a value exists anywhere in the range of ("H9:AP9") Return the Header. I would need the returned headers to be side by side without any blanks in between.

The results can be on a secondary sheet.


Sample Layout:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Row Description[/TD]
[TD]Header1[/TD]
[TD]Header2[/TD]
[TD]Header3[/TD]
[TD]Header4[/TD]
[TD]Header5[/TD]
[/TR]
[TR]
[TD]Misc 1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Misc 2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


So for Misc 1: The returned values will look like: Header2, Header 4

So for Misc 2: The returned values will look like: Header1, Header2, Header 3

Each returned value will be in a new column left to right so that I can copy each value in one cell.

Hopefully this is somewhat clear.

 

[Fluff]

With data like

Book1
	A	B	C	D	E	F
1	Row Description	Header1	Header2	Header3	Header4	Header5
2	Misc 1		1		2
3	Misc 2	1	1	1
Sheet1

Try

Book1
	A	B	C	D
1
2	Misc 1	Header2	Header4
3	Misc 2	Header1	Header2	Header3
Sheet2

Cell Formulas
Range	Formula
B2	=IFERROR(INDEX(Sheet1!$B$1:$F$1,AGGREGATE(15,6,(COLUMN(Sheet1!$B$1:$F$1)-COLUMN(Sheet1!$B$1)+1)/((Sheet1!$A$2:$A$3=$A2)*(Sheet1!$B$2:$F$3<>"")),COLUMNS($A$1:A$1))),"")

Just copy the formula down & across

 

[Johnny Thunder]

Can you explain what the 15,6 means?

 

[Fluff]

You'll find all the arguments here https://support.office.com/en-us/article/aggregate-function-43b9278e-6aa7-4f17-92b6-e19993fa26df