INDEX-MATCH with a vertical lookup

[Darren_workforce]

I am working off of 2 tabs. The 'source' tab is what my supervisor updates so we know the team assignment breakdown and who falls under which manager. There are more columns but I am hoping 2 groups is enough. Row 2 is where each of the team manager names is entered.

On my second 'lookup' tab, I have the following formula:

=INDEX(source!B2:P2,MATCH(A2,source!B3:P33))

My hopes were to add an agent name down Column A of my 'lookup' tab and have it generate the manager name in Column B of the same tab. However, I'm getting an #N/A error. I've looked up a few websites and am not sure what it is I am doing wrong. The only thing I am guessing is that instead of looking left and right, I'm wanting to look up from the location of the agent name so I need to account for that?

Example: If I enter "Mary Thomas" into Column A of the 'lookup' tab, the results in the adjacent B cell would be "James"
Brittany Black would result in "Sydney"

SOURCE TAB:

 

[shift-del]

There are lots of possibilities.

Mappe16
	A	B
2	Aaron Alexander	Sydney
Tabelle2

Cell Formulas
Range	Formula
B2	B2	=IF(COUNTIFS(Source!B6:B16,A2),Source!B2,"")&IF(COUNTIFS(Source!F6:F16,A2),Source!F2,"")

 

[Cubist]

An option for when you have multiple columns. This assumes your data starts in column B.

Book1
	A	B	C	D	E	F	G	H	I
1		James		Sarah		Sydney		H	Sarah
2		A		E		C
3		B		F		D
4				G
5				H
Sheet2

Cell Formulas
Range	Formula
I1	I1	=INDEX($B$1:$F$1,1,MIN(IF($B$2:$F$5=H1,COLUMN($B$1:$F$5)-1)))

 

[Darren_workforce]

Good morning! Apologies for the delayed response. I tested both and both work. However, is there a way to be inclusive for additional columns without having to add additional

There are lots of possibilities.

Mappe16
	A	B
2	Aaron Alexander	Sydney
Tabelle2

Cell Formulas
Range	Formula
B2	B2	=IF(COUNTIFS(Source!B6:B16,A2),Source!B2,"")&IF(COUNTIFS(Source!F6:F16,A2),Source!F2,"")

thank you! this did work but I will have additional columns as managers are added and would rather not include additional IF statements.

 

[Darren_workforce]

An option for when you have multiple columns. This assumes your data starts in column B.

Book1
	A	B	C	D	E	F	G	H	I
1		James		Sarah		Sydney		H	Sarah
2		A		E		C
3		B		F		D
4				G
5				H
Sheet2

Cell Formulas
Range	Formula
I1	I1	=INDEX($B$1:$F$1,1,MIN(IF($B$2:$F$5=H1,COLUMN($B$1:$F$5)-1)))

OK this seems to do exactly what is needed. I adjusted the end column and it seems to grab all data exactly as needed. Thank you @Cubist