Function to result column header based on text in a column

[cb743]

Hello, I am relatively new to excel. I want to create a function that will search for a name and result the heading (team).

Capitals	Wizards	Nationals	Redskins
James	Corey	Hannah	Test1
Mike	Amy	Test3	Test2

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Each heading will have around 10 users.

An example of the end result that I want is insert corey into a cell and have it output the team corey is on

User	Team

<tbody>
</tbody>

 

[Yongle]

one way

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Capitals	Wizards	Nationals	Redskins	.	User	Team	Formula (copied down)
2​	N001	N101	N201	N301		N313	Redskins	=IF(COUNTIF(A:A,F2)>0,$A$1,IF(COUNTIF(B:B,F2)>0,$B$1,IF(COUNTIF(C:C,F2)>0,$C$1,IF(COUNTIF(D:D,F2)>0,$D$1,"Not Found"))))
3​	N002	N102	N202	N302		N004	Capitals
4​	N003	N103	N203	N303		N312	Redskins
5​	N004	N104	N204	N304		Matey	Not Found
6​	N005	N105	N205	N305
7​	N006	N106	N206	N306
8​	N007	N107	N207	N307
9​	N008	N108	N208	N308
10​	N009	N109	N209	N309
11​	N010	N110	N210	N310
12​	N011	N111	N211	N311
13​	N012	N112	N212	N312
14​	N013	N113	N213	N313
15​	N014	N114	N214	N314

Sheet: Sheet1

 

[FormR]

Welcome to the forum! Here is another option you can try.

Excel 2013/2016
	A	B	C	D	E	F	G
1	Capitals	Wizards	Nationals	Redskins		corey	Wizards
2	James	Corey	Hannah	Test1
3	Mike	Amy	Test3	Test2
Sheet1

Cell Formulas
Range	Formula
G1	=INDEX($A$1:$D$1,AGGREGATE(15,6,COLUMN($A$1:$D$1)-MIN(COLUMN($A$1:$D$1))+1/($A$1:$D$3=F1),1))