Return Table Header from Value in Table

[MarkAn]

Hi
I am trying to do the following:
On Sheet3, I have a table, the table headers are Names of Team Leaders I.e. TeamA, TeamB, TeamC etc.
The table lists the names of the team members under each leader I.e. TeamA has MemberAA, MemberAB, MemberAC etc.

On Sheet2, I have the list of all the Members in ColumnA
In ColumnB, I would like to input a formula that will search the table on Sheet3 and return the Header/Team Leaders name they are assigned to.

I have tried Index&Match, and XLookup and nothing will search the whole table and return the corresponding Team Leader name.

Can anyone please help.
Regards

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Is the table on Sheet3 a formal Excel table (ListObject) - usually identified by colour-band formatting?

 

[MarkAn]

Hi

Yes I formatted the table into a formal Excel table (should I unformat it?)
I believe my office are using Excel 2010

Thanks
MarkAn

 

[MarkAn]

Hi

Yes I formatted the table into a formal Excel table (should I unformat it?)
I believe my office are using Excel 2010

Thanks
MarkAn

Or version 2108

 

[Peter_SSs]

Thanks for updating your details.

See if this would work for you. In the formula, check/adjust the table name & heading of the first table column.

MarkAn.xlsm
	A	B	C
1	TeamA	TeamB	TeamC
2	MemberA1	MemberB1	MemberC1
3	MemberA2	MemberB2	MemberC2
4	MemberA3	MemberB3	MemberC3
5	MemberA4	MemberB4	MemberC4
6	MemberA5	MemberB5	MemberC5
Sheet3

MarkAn.xlsm
	A	B
1	Name	Team
2	MemberB3	TeamB
3	MemberA2	TeamA
4	MemberA1	TeamA
5	MemberA4	TeamA
6	MemberA5	TeamA
7	MemberA3	TeamA
8	MemberB4	TeamB
9	MemberC5	TeamC
10	MemberB2	TeamB
11	MemberC2	TeamC
12	MemberC4	TeamC
13	MemberB1	TeamB
14	MemberC3	TeamC
15	MemberB5	TeamB
16	MemberC1	TeamC
Sheet2

Cell Formulas
Range	Formula
B2:B16	B2	=INDEX(Table1[#Headers],AGGREGATE(15,6,(COLUMN(Table1)-COLUMN(Table1[TeamA])+1)/(Table1=A2),1))

 

[MarkAn]

Thanks for updating your details.

See if this would work for you. In the formula, check/adjust the table name & heading of the first table column.

MarkAn.xlsm
	A	B	C
1	TeamA	TeamB	TeamC
2	MemberA1	MemberB1	MemberC1
3	MemberA2	MemberB2	MemberC2
4	MemberA3	MemberB3	MemberC3
5	MemberA4	MemberB4	MemberC4
6	MemberA5	MemberB5	MemberC5
Sheet3

MarkAn.xlsm
	A	B
1	Name	Team
2	MemberB3	TeamB
3	MemberA2	TeamA
4	MemberA1	TeamA
5	MemberA4	TeamA
6	MemberA5	TeamA
7	MemberA3	TeamA
8	MemberB4	TeamB
9	MemberC5	TeamC
10	MemberB2	TeamB
11	MemberC2	TeamC
12	MemberC4	TeamC
13	MemberB1	TeamB
14	MemberC3	TeamC
15	MemberB5	TeamB
16	MemberC1	TeamC
Sheet2

Cell Formulas
Range	Formula
B2:B16	B2	=INDEX(Table1[#Headers],AGGREGATE(15,6,(COLUMN(Table1)-COLUMN(Table1[TeamA])+1)/(Table1=A2),1))

That has worked a treat, I appreciate your help

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.