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:

Row Description	Header1	Header2	Header3	Header4	Header5
Misc 1		1		2
Misc 2	1	1	1

<tbody>
</tbody>

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