Using XLOOKUP and returning multiple results

[morrowj04]

Hi

Ive tried various concoctions of formulas to get this to work.

Im Stuck now and have no idea so its time i reach out to the specialists

I have two sheets

One called Colleagues and another called Live.

Colleagues sheet is a list of names and Departments

Live Sheet is Department.

What im trying to do is using XLOOKUP pull the inofrmation from the colleague sheet and only put the names of the people in the Live Department in the Live sheet.

Now in my formula on the Live Sheet,
I use the MID function to get the name of the sheet which forms part of the larger formula to get the colleague name.

I would like each colleagues name to be in the relevant sheet. but this only pulls the first one it finds.

Can you help me achieve this, i tried FILTER but it didnt work but it could just be me not fully understanding the function usage.


Thanks in Advance for your help




Colleagues Sheet

testspread.xlsx
	A	B
1	Colleague Name	team
2	JA	Testing
3	JC	Testing
4	JD	Testing
5
6
7	JG	Production
8	JH	Production
9
10	JI	Live
11	JK	Live
12	JL	Live
Colleague

Live Sheet

testspread.xlsx
	A	B
1	JI
2	JI
3	JI
4
5
6
7
8
9
10
Live

Cell Formulas
Range	Formula
A1:A2	A1	=XLOOKUP("*"&MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)&"*",Colleague!B2:B100,Colleague!A2:A100,,2)
A3	A3	=XLOOKUP("*"&MID(CELL("filename",A3),FIND("]",CELL("filename",A3))+1,256)&"*",Colleague!B3:B102,Colleague!A3:A102,,2)

 

[AhoyNC]

Try:
Colleague Sheet

Book2
	A	B
1	Colleague Name	team
2	JA	Testing
3	JC	Testing
4	JD	Testing
5
6
7	JG	Production
8	JH	Production
9
10	JI	Live
11	JK	Live
12	JL	Live
Colleague

Live Sheet

Book2
	A
1	JI
2	JK
3	JL
Live

Cell Formulas
Range	Formula
A1:A3	A1	=FILTER(Colleague!$A$2:$A$12,Colleague!$B$2:$B$12="Live","No Data")
Dynamic array formulas.