match and return column headers

[excellingatnothing]

Find	Return	2	0		Header1	Header2	Header3	Header 4	Header 5
hotel	Header2				alpha	beta	beta	alpha	beta
	Header4				beta	charlie	echo	hotel	golf
						hotel

<tbody>
</tbody>

copied this from somewhere but can't implement it on mine. i'm a total excel noob, excuse the absolute $ signs.
basically i have a large excel with different products, each product could potentially contain the same documents, and i want to be able to find one document and have it output all of the products it is included in. i got it to work but it stopped working when the documents list exceeded 20.

C1 = COUNTIF($F$2:AF20,"="&A2)

B2 = IF(COLUMNS($B$2:$B$2)>($C$1-$D$1)," ",INDEX($F$1:$AF$1,SMALL(IF($F$2:$AF$2=$A$1,COLUMN($F$2:$AF$99)-COLUMN($F$2)+1),COLUMNS($B$2:B2))))

B3 = IF(COLUMNS($B$2:$B$2)>($C$1-$D$1)," ",INDEX($F$1:$AF$1,SMALL(IF($F$2:$AF$2=$A$1,COLUMN($F$2:$AF$99)-COLUMN($F$2)+1),COLUMNS($B$2:C2))))

B4 = ...

 

[sheetspread]

The reverse pivot table is much better than writing a formula here:

https://www.youtube.com/watch?v=xmqTN0X-AgY

 

[excellingatnothing]

Product A

Product A

Product A

Product B

Product B

Product B

Product C

Product C

Product C

Product D

Product D

Product D

Product E

Product E

Product E

Doc Code

Last Submitted

Revision

Doc Code

Last Submitted

Revision

Doc Code

Last Submitted

Revision

Doc Code

Last Submitted

Revision

Doc Code

Last Submitted

Revision

FIND

124

123

111

123

124

1

124

112

abc1

457

5

DOCS

Product A

234

124

666

10

Product B

344

345

Product D

456

567

abc1

<tbody>
</tbody>

Maybe this is more clear with the actual columns I have.
Each product further divided into document code, last submitted date, and revision.

I want to FIND a certain document, from ALL the products, and output the product names that include the document. Every product is different, most doc codes are different but can be shared. Submitted dates are all different. Revisions are the last revision noted during the date submitted.

 

[excellingatnothing]

The repeated Product Names are merged cells.

 

[sheetspread]

The reverse pivot table above will do this: delete the second row and be sure there's a header over each column (merged cells usually sacrifice function for style and end up with neither). Then you can retrieve the original, normal format of the table:

Excel 2010
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1		Product A	Product A	Product A	Product B	Product B	Product B	Product C	Product C	Product C	Product D	Product D	Product D	Product E	Product E	Product E
2	1	123			111			123			124			1
3	2	124			112			abc1			457			5
4	3	234			124						666			10
5	4	344			345
6	5	456			567
7	6	abc1
Sheet10 (2)

to:

Excel 2010
	A	B	C
1	Row	Column	Value
11	1	Product D	124
17	2	Product A	124
35	3	Product B	124
Sheet18

 

[sheetspread]

But if you must have a formula:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1				Product A	Product A	Product A	Product B	Product B	Product B	Product C	Product C	Product C	Product D	Product D	Product D	Product E	Product E	Product E
2	FIND	124		123			111			123			124			1
3				124			112			abc1			457			5
4	DOCS	Product A		234			124						666			10
5		Product B		344			345
6		Product D		456			567
7				abc1
Sheet10 (2)

Cell Formulas
Range	Formula
B4	{=INDEX($D$1:$R$1,SMALL(IF($D$2:$R$7=$B$2,COLUMN($D$2:$R$7)),ROW(A1))-2)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[excellingatnothing]

THANK YOU! That shorter formula worked. Just changed the -2 to -5 and adjusted the cell locations. I didn't realize that was to offset the location.