Find partial text match and then list out the header row for the match

[gberg]

I have a table with headers "1", "2", "3", "4" and "5". The data in the table will be "A", "B", "C", "1", "2" or "3". The data can be a single reference or can be a combination of the items. In another table to the side of the data table I have the headers "A", "B", "C", "1", "2" and "3". I need a formula to list out the data header that the appropriate character is in. The example below (no formulas) shows the desired output. The actual table I have is much larger, I just didn't know how to post the whole thing so I hope this is enough for a formula.

1	2	3	4	5		A	B	C	1	2	3
2		3B	A1	C		3​	3​	5​	4​	1​	3​
3	BC2	1A				3​	2​	2​	3​	2​	1​

 

[Cubist]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	1	2	3	4	5		A	B	C	1	2	3
2	2		3B	A1	C		4	3	5	4	1	3
3	3	BC2	1A				3	2	2	3	2	1
Sheet3

Cell Formulas
Range	Formula
G2:L3	G2	=TOCOL(IF(ISNUMBER(SEARCH(G$1,$A2:$E2)),$A$1:$E$1,NA()),2)

 

[gberg]

Thank you so much for your help, that works great! This board never ceases to amaze me with all the knowledgeable members!

 

[Cubist]

You're welcome.