Look at mutiple columns and return column header where the text matches

[jezmechanic]

Hi Folks

I am looking for some help.

So in "Case" tab I have a list of cases the company are dealing with. In column "I" within this tab contains the name of the worker who dealt with the case. In a separate tab ("Team Sheets") I have all the workers in the company listed based on which team they are in (example - Column "A" header is "Steve's Team" then below is a list of all the workers in Steve's team). I am looking for a formula that will look at the name of the worker within the "Case" tab (Column "I"), cross reference with the "Team Sheets" tab and return the header for whatever column the workers name matched.

Thanks
Jez

 

[Marcelo Branco]

Welcome to Mr Excel forum

Could you tell us where are the headers (row) and data (rows/columns) in Team Sheets tab?

M.

 

[jezmechanic]

Hi Marcelo, thank you.

Yeah so the columns in "Team Sheets" are columns A:P and the managers name are row 2 for each column followed by the workers listed immediately below.

Thanks
Jez

 

[Marcelo Branco]

Ok
Headers in A2:P2
Question
How many rows of data do you have? Rows 3 to 100, for example

M.

 

[jezmechanic]

Not all teams are the same size but the range to cover them all would be A2:P17

 

[Marcelo Branco]

Maybe something like this

Team Sheets tab

	A​	B​	C​
1​
2​	Steve's team​	Mark's team​	....​
3​	Bob​	Mary​
4​	John​	Richard​
5​		William​
6​

Case tab

	I​	J​
1​	Names​	Team​
2​	Bob​	Steve's team​
3​	Mary​	Mark's team​
4​	John​	Steve's team​
5​	Richard​	Mark's team​
6​	William​	Mark's team​

Formula in J2 copied down
=INDEX('Team Sheets'!$A$2:$P$2,AGGREGATE(14,6,('Team Sheets'!$A$3:$P$17=I2)*COLUMN('Team Sheets'!$A$2:$P$2),1))

Hope this helps

M.

 

[jezmechanic]

Thank you very much Marcelo, this is a great help!!!!

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.