match and return column headers

[excellingatnothing]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Find[/TD]
[TD]Return[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]Header1[/TD]
[TD]Header2[/TD]
[TD]Header3[/TD]
[TD]Header 4[/TD]
[TD]Header 5[/TD]
[/TR]
[TR]
[TD]hotel[/TD]
[TD]Header2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]alpha[/TD]
[TD]beta[/TD]
[TD]beta[/TD]
[TD]alpha[/TD]
[TD]beta[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Header4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]beta[/TD]
[TD]charlie[/TD]
[TD]echo[/TD]
[TD]hotel[/TD]
[TD]golf[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]hotel[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Product A[/TD]
[TD]Product A[/TD]
[TD]Product A[/TD]
[TD]Product B[/TD]
[TD]Product B[/TD]
[TD]Product B[/TD]
[TD]Product C[/TD]
[TD]Product C[/TD]
[TD]Product C[/TD]
[TD]Product D[/TD]
[TD]Product D[/TD]
[TD]Product D[/TD]
[TD]Product E[/TD]
[TD]Product E[/TD]
[TD]Product E[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Doc Code[/TD]
[TD]Last Submitted[/TD]
[TD]Revision[/TD]
[TD]Doc Code[/TD]
[TD]Last Submitted[/TD]
[TD]Revision[/TD]
[TD]Doc Code[/TD]
[TD]Last Submitted[/TD]
[TD]Revision[/TD]
[TD]Doc Code[/TD]
[TD]Last Submitted[/TD]
[TD]Revision[/TD]
[TD]Doc Code[/TD]
[TD]Last Submitted[/TD]
[TD]Revision[/TD]
[/TR]
[TR]
[TD]FIND[/TD]
[TD]124[/TD]
[TD]123[/TD]
[TD][/TD]
[TD][/TD]
[TD]111[/TD]
[TD][/TD]
[TD][/TD]
[TD]123[/TD]
[TD][/TD]
[TD][/TD]
[TD]124[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]124[/TD]
[TD][/TD]
[TD][/TD]
[TD]112[/TD]
[TD][/TD]
[TD][/TD]
[TD]abc1[/TD]
[TD][/TD]
[TD][/TD]
[TD]457[/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DOCS[/TD]
[TD]Product A[/TD]
[TD]234[/TD]
[TD][/TD]
[TD][/TD]
[TD]124[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]666[/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Product B[/TD]
[TD]344[/TD]
[TD][/TD]
[TD][/TD]
[TD]345[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Product D[/TD]
[TD]456[/TD]
[TD][/TD]
[TD][/TD]
[TD]567[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]abc1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.