Index/Aggregate Help - Listing All Active Goalies on an NHL Team

[AYouQueTai]

Hello,

I am trying to list all active goalies from the Anaheim Sheet and Table and list them into Cells A2:A10 on the Goalies Sheet. (in this case there are 2 results but there could be 10 in a season)
So the names would be Dostal, Lukas and Clang, Calle ([pos]="G" and [Player Status]="Active")
Anyone know how to accomplish this?
Thanks,

NHL 2024-2025.xlsx
	A	B	C
1	Anaheim	Boston	Buffalo
2			Test
3			Test2
4			Test3
5
6
7
8
9
10
11
12
Goalies

NHL 2024-2025.xlsx
	A	B	C	D	E	F
1	Jersey	Start Date	End Date	Pos	Name	Player Status
2	11	Oct-03-2024	Oct-04-2025	LW	Zegras, Trevor	Active
3	23	Oct-03-2024	Oct-04-2025	C	Mctavish, Mason	Active
4	19	Oct-03-2024	Oct-04-2025	RW	Terry, Troy	Active
5	61	Oct-03-2024	Oct-04-2025	LW	Gauthier, Cutter	Active
6	91	Oct-03-2024	Oct-04-2025	C	Carlson, Leo	Active
7	17	Oct-03-2024	Oct-04-2025	RW	Killorn, Alex	Active
8	13	Oct-03-2024	Oct-04-2025	LW	Fabbri, Robby	Active
9	16	Oct-03-2024	Oct-04-2025	C	Strome, Ryan	Active
10	77	Oct-03-2024	Oct-04-2025	RW	Vatrano, Frank	Active
11	44	Oct-03-2024	Oct-04-2025	LW	Johnston, Ross	Active
12	21	Oct-03-2024	Oct-04-2025	C	Lundestrom, Isac	Active
13	26	Oct-03-2024	Oct-04-2025	RW	Mcginn, Brock	Active
14	04	Oct-03-2024	Oct-04-2025	LD	Fowler, Cam	Active
15	51	Oct-03-2024	Oct-04-2025	RD	Zellweger, Olen	Active
16	60	Oct-03-2024	Oct-04-2025	LD	Lacombe, Jackson	Active
17	07	Oct-03-2024	Oct-04-2025	RD	Gudas, Radko	Active
18	06	Oct-03-2024	Oct-04-2025	LD	Dumoulin, Brian	Active
19	34	Oct-03-2024	Oct-04-2025	RD	Mintyukov, Pavel	Active
20	01	Oct-03-2024	Oct-04-2025	G	Dostal, Lukas	Active
21	36	Oct-03-2024	Oct-04-2025	G	Gibson, John	Injured
22		Oct-03-2024	Oct-04-2025	G	Clang, Calle	Active
Anaheim

Cells with Data Validation
Cell	Allow	Criteria
F2:F22	List	=INDIRECT("PlayerStatus")

 

[dreid1011]

Perhaps this:

Book1
	A	B	C
1	Anaheim	Boston	Buffalo
2	Dostal, Lukas		Test
3	Clang, Calle		Test2
4			Test3
Goalies

Cell Formulas
Range	Formula
A2:A3	A2	=FILTER(Anaheim!E2:E22,(Anaheim!D2:D22="G")*(Anaheim!F2:F22="Active"))
Dynamic array formulas.

Unless your data is in tables.

 

[AYouQueTai]

Hmm, I get a #Spill error when I try that

 

[AYouQueTai]

Hmm, I get a #Spill error when I try that

Multi cell Array formulas aren't allowed in Tables Error message when I try Ctrl+Shift+enter

 

[dreid1011]

Multi cell Array formulas aren't allowed in Tables Error message when I try Ctrl+Shift+enter

Right, I realized that after. Your source data on the team sheets can be in tables, but it won't work if your goalie sheet is in a table.

 

[AYouQueTai]

I didn't really need the goalies table. Thank you very much, it works great

 

[dreid1011]

You're welcome, and happy to help.