INDEX MATCH-MATCH Help

[surkdidat]

On may main data sheet I have a list of Employee Names, store IDs and Job Titles (the data goes over 5 job titles and 3000 stores that I need to extract this for)

Store ID	Employee Name	Job Title
0001	Steve	Assistant
0001	Peter	Cleaner
0001	Andy	Manager
0002	Ellie	Assistant
0002	Sophia	Cleaner
0002	Ameila	Manager

What I am looking to return is

	Assistant	Cleaner	Manager
0001	Steve	Peter	Andy
0002	Ellie	Sophia	Ameila

I am getting stuck with my INDEx-MATCH-MATCH, please can someone help!

 

[hagia_sofia]

Do you have headers in your second table and you are only looking to populate the blank table or is it something that is supposed to be returned as well (i.e. both headers and names)?

 

[surkdidat]

Do you have headers in your second table and you are only looking to populate the blank table or is it something that is supposed to be returned as well (i.e. both headers and names)?

Its not in an "official Excel Table" (ie not converted into one), but the names are written in row A,

A1 - Store ID	B1 - Employee Name	C1 - Job Title
A2 - 0001	B2 - Steve	C2 - Assistant
A3 - 0001	B3 - Peter	C3 - Cleaner
A4 - 0001	B4 - Andy	C4 - Manager
A5 - 0002	B5 - Ellie	C5 - Assistant
A6 - 0002	B6 - Sophia	C6 - Cleaner
A7 - 0002	B7 - Ameila	C7 - Manager

 

[hagia_sofia]

That is not what I meant by anyway please test this:

=XLOOKUP(A11:A12&B10:D10,A2:A7&C2:C7,B2:B7)

where A11:A12 represents 0001 and 0002 from the 2nd table and B10:D10 represents Assistant - Cleaner - Manager

 

[Cubist]

Try:

Book1
	A	B	C	D
1	Store ID	Employee Name	Job Title
2	0001	Steve	Assistant
3	0001	Peter	Cleaner
4	0001	Andy	Manager
5	0002	Ellie	Assistant
6	0002	Sophia	Cleaner
7	0002	Ameila	Manager
8
9		Assistant	Cleaner	Manager
10	0001	Steve	Peter	Andy
11	0002	Ellie	Sophia	Ameila
Sheet1

Cell Formulas
Range	Formula
B9:D9	B9	=TOROW(UNIQUE(C2:C7))
A10:A11	A10	=UNIQUE(A2:A7)
B10:D11	B10	=INDEX($B$2:$B$7,XMATCH($A10&"|"&B$9,$A$2:$A$7&"|"&$C$2:$C$7))
Dynamic array formulas.

 

[surkdidat]

Thank you Cubist and Hagia_sofia!

Try:
[XD=c:548235]=TOROW(UNIQUE(C2:C7))[/XD][/XR][XR][XD]A10:A11[/XD][XD=fw:b]A10[/XD][XD=c:548235]=UNIQUE(A2:A7)[/XD][/XR][XR][XD]B10:D11[/XD][XD=fw:b]B10[/XD][XD]=INDEX($B$2:$B$7,XMATCH($A10&"|"&B$9,$A$2:$A$7&"|"&$C$2:$C$7))[/XD][/XR][/RANGE]

That is not what I meant by anyway please test this:

=XLOOKUP(A11:A12&B10:D10,A2:A7&C2:C7,B2:B7)

where A11:A12 represents 0001 and 0002 from the 2nd table and B10:D10 represents Assistant - Cleaner - Manager