Return Column Headers for Cells Matching Text

[FuriousD]

Sorry if this has been handled. I searched and couldn't find this exact situation.

I'm trying to use index/match or similar to compare text (a name) in one cell to that of 4 other cells (columns). Then for whichever cell contains the match, my result is the column header. In the table below, I am using A2 to search B2:E2. When there is a match in the text, return the corresponding header title from B1:E1.

	A	B	C	D	E	F
1	Completed By	Manager	Recruiter	Assistant	Admin	Completed By Role
2	Spano, Jessie	Spano, Jessie	Slater, AC	Morris, Zach	Kapowski, Kelly	Manager
3	Barakus, BA	Face	Hannibal	Barakus, BA	Murdock	Assistant
4	D, Furious	Lil Helper, Santas	D, Furious	V, Snowball	Poochie	Recruiter

TIA

 

[Fluff]

How about

+Fluff v2.xlsm
	A	B	C	D	E	F
1	Completed By	Manager	Recruiter	Assistant	Admin	Completed By Role
2	Spano, Jessie	Spano, Jessie	Slater, AC	Morris, Zach	Kapowski, Kelly	Manager
3	Barakus, BA	Face	Hannibal	Barakus, BA	Murdock	Assistant
4	D, Furious	Lil Helper, Santas	D, Furious	V, Snowball	Poochie	Recruiter
Master

Cell Formulas
Range	Formula
F2:F4	F2	=INDEX($B$1:$E$1,AGGREGATE(15,6,(COLUMN($B$1:$E$1)-COLUMN($B$1)+1)/($B$2:$E$4=A2),1))

 

[FuriousD]

How about

+Fluff v2.xlsm
	A	B	C	D	E	F
1	Completed By	Manager	Recruiter	Assistant	Admin	Completed By Role
2	Spano, Jessie	Spano, Jessie	Slater, AC	Morris, Zach	Kapowski, Kelly	Manager
3	Barakus, BA	Face	Hannibal	Barakus, BA	Murdock	Assistant
4	D, Furious	Lil Helper, Santas	D, Furious	V, Snowball	Poochie	Recruiter
Master

Cell Formulas
Range	Formula
F2:F4	F2	=INDEX($B$1:$E$1,AGGREGATE(15,6,(COLUMN($B$1:$E$1)-COLUMN($B$1)+1)/($B$2:$E$4=A2),1))

Thanks - I'm getting the #NUM! Error when I update to my actual column/rows. Maybe I screwed something up in translation? I'll update to my actual columns...

 

[FuriousD]

How about

+Fluff v2.xlsm
	A	B	C	D	E	F
1	Completed By	Manager	Recruiter	Assistant	Admin	Completed By Role
2	Spano, Jessie	Spano, Jessie	Slater, AC	Morris, Zach	Kapowski, Kelly	Manager
3	Barakus, BA	Face	Hannibal	Barakus, BA	Murdock	Assistant
4	D, Furious	Lil Helper, Santas	D, Furious	V, Snowball	Poochie	Recruiter
Master

Cell Formulas
Range	Formula
F2:F4	F2	=INDEX($B$1:$E$1,AGGREGATE(15,6,(COLUMN($B$1:$E$1)-COLUMN($B$1)+1)/($B$2:$E$4=A2),1))

	H	I	J	K	L	M
1	Completed By	Manager	Recruiter	Assistant	Admin	Completed By Role
2	Spano, Jessie	Spano, Jessie	Slater, AC	Morris, Zach	Kapowski, Kelly	Manager
3	Barakus, BA	Face	Hannibal	Barakus, BA	Murdock	Assistant
4	D, Furious	Lil Helper, Santas	D, Furious	V, Snowball	Poochie	Recruiter

Updated the Columns to reflect where they are actually in my sheet. TIA

 

[Fluff]

How did you change the formula?

 

[FuriousD]

How did you change the formula?

Just the Column letters, as reflected above...

=INDEX($I$1:$L$1,AGGREGATE(15,6,(COLUMN($I$1:$L$1)-COLUMN($I$1)+1)/($I$2:$L$4=H2),1))

 

[Fluff]

That's fine, which means you don't have a match. If you think there should be a match check for leading/trailing spaces, typos etc