I need help with this array formula

[valmir]

Hi! Everyone
I have this array formula in L:S that pulls data from B:J. Is it possible to apply a filter or something so that only values named ACA in column C (with the respective row matches) are pulled out?
Thanks!

PRI COMPETITIONS test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
5	1	02-10-2021	ACA	1			1	0	+1	3		02-10-2021	ACA	1			1	0	+1
6	2	03-11-2021	SBE	1			5	0	+5	3		26-06-2021	ACA			1	1	3	-2
7	3	20-10-2021	PET		1		2	2	0	1		17-02-2021	ACA	1			2	0	+2
8	4	27-10-2021	INT	1			2	1	+1	3		15-12-2019	ACA	1	0	0	3	0	+3
9	5	31-10-2021	CCU			1	0	1	-1	0		00-01-1900	ACA						0
10	6	07-11-2021	CAA		1		0	0	0	1		00-01-1900	ACA	0	0	0	0	0	0
11	7	00-01-1900	LIB						0	0		30-06-2021	BAI	1			3	0	+3
12	8	00-01-1900	DLS				0	0	0	0		31-03-2021	BAI	1			2	0	+2
13	9	00-01-1900	KAB						0	0		07-11-2021	CAA		1		0	0	0
14	10	00-01-1900	DES				0	0	0	0		16-05-2021	CAA	1			2	1	+1
15	11	00-01-1900	MAQ						0	0		13-04-2021	CAA	1			4	0	+4
16	12	00-01-1900	WIL				0	0	0	0		23-02-2020	CAA	0	0	1	0	1	+1
17	13	00-01-1900	PRO						0	0		20-10-2019	CAA	1	0	0	4	0	+4
18	14	00-01-1900	SCC				0	0	0	0		00-01-1900	CAA				0	0	0
19	15	00-01-1900	SAG						0	0		31-10-2021	CCU			1	0	1	-1
20	16	00-01-1900	ACA						0	0		02-05-2021	CCU	1			2	0	+2
21	17	00-01-1900	SBE				0	0	0	0		20-03-2021	CCU	1			2	0	+2
22	18	00-01-1900	PET						0	0		01-03-2020	CCU	1	0	0	2	1	+3
23	19	00-01-1900	INT				0	0	0	0		06-10-2019	CCU	1	0	0	1	0	+1
24	20	00-01-1900	CCU						0	0		00-01-1900	CCU						0
25	21	00-01-1900	CAA				0	0	0	0		12-05-2021	DES	1			2	0	+2
26	22	00-01-1900	LIB				0	0	0	0		17-03-2021	DES		1		0	0	0
27	23	00-01-1900	DLS						0	0		12-02-2020	DES	1	0	0	1	0	+1
28	24	00-01-1900	KAB				0	0	0	0		20-08-2019	DES	1	0	0	2	0	+2
29	25	00-01-1900	DES						0	0		00-01-1900	DES				0	0	0
30	26	00-01-1900	MAQ				0	0	0	0		00-01-1900	DES						0
31	27	00-01-1900	WIL						0	0		00-01-1900	DLS				0	0	0
32	28	00-01-1900	PRO				0	0	0	0		00-01-1900	DLS						0
33	29	00-01-1900	SCC						0	0		31-07-2021	FER	1			4	1	+3
34	30	00-01-1900	SAG				0	0	0	0		14-03-2021	FER	1			3	0	+3
35												08-02-2020	FER	0	1	0	1	1	2
36												19-09-2019	FER	1	0	0	2	0	2
37												27-10-2021	INT	1			2	1	1
38	1	29-12-2020	INT	1			1	0	+1	3		24-04-2021	INT	1			1	0	1
39	2	20-03-2021	CCU	1			2	0	+2	3		29-12-2020	INT	1			1	0	1
40	3	18-04-2021	WIL		1		2	2	0	1		08-03-2020	INT	1	0	0	2	0	2
41	4	17-03-2021	DES		1		0	0	0	1		13-10-2019	INT	1	0	0	1	0	1
42	5	13-04-2021	CAA	1			4	0	+4	3		00-01-1900	INT				0	0	0
43	6	06-04-2021	SAG			1	1	2	-1	0		00-01-1900	KAB						0
44	7	10-04-2021	PRO	1			2	1	+1	3		00-01-1900	KAB				0	0	0
45	8	06-02-2021	MAQ			1	2	3	-1	0		16-07-2021	LIB		1		1	1	0
46	9	07-02-2021	PET	1			1	0	+1	3		03-04-2021	LIB		1		2	2	0
47	10	03-04-2021	LIB		1		2	2	0	1		23-11-2019	LIB	1	0	0	4	1	5
48	11	17-02-2021	ACA	1			2	0	+2	3		00-01-1900	LIB						0
49	12	31-03-2021	BAI	1			2	0	+2	3		00-01-1900	LIB				0	0	0
50	13	28-02-2021	SCC	1			2	1	+1	3		00-01-1900	LIB	0	0	0	0	0	0
51	14	07-03-2021	SRC	1			2	0	+2	3		27-10-2019	MAI	0	0	0	0	0	0
52	15	14-03-2021	FER	1			3	0	+3	3		00-01-1900	MAI	0	0	0	0	0	0
53	16	24-04-2021	INT	1			1	0	+1	3		05-06-2021	MAQ		1		0	0	0
54	17	02-05-2021	CCU	1			2	0	+2	3		06-02-2021	MAQ			1	2	3	-1
55	18	09-05-2021	WIL		1		1	1	0	1		14-03-2020	MAQ	1	0	0	3	1	4
56	19	12-05-2021	DES	1			2	0	+2	3		23-10-2019	MAQ	1	0	0	3	1	4
57	20	16-05-2021	CAA	1			2	1	+1	3		00-01-1900	MAQ						0
58	21	23-05-2021	SAG		1		0	0	0	1		00-01-1900	MAQ				0	0	0
59	22	29-05-2021	PRO	1			1	0	+1	3		20-10-2021	PET		1		2	2	0
60	23	05-06-2021	MAQ		1		0	0	0	1		12-06-2021	PET			1	0	3	-3
61	24	12-06-2021	PET			1	0	3	-3	0		07-02-2021	PET	1			1	0	1
62	25	16-07-2021	LIB		1		1	1	0	1		03-11-2019	PET	0	0	1	0	2	2
63	26	26-06-2021	ACA			1	1	3	-2	0		00-01-1900	PET						0
64	27	30-06-2021	BAI	1			3	0	+3	3		00-01-1900	PET	0	0	0	0	0	0
65	28	19-07-2021	SCC	1			4	0	+4	3		29-05-2021	PRO	1			1	0	1
66	29	24-07-2021	SRC	1			4	2	+2	3		10-04-2021	PRO	1			2	1	1
67	30	31-07-2021	FER	1			4	1	+3	3		19-01-2020	PRO	1	0	0	4	0	4
68												27-08-2019	PRO	0	0	1	0	1	1
69												00-01-1900	PRO						0
70												00-01-1900	PRO				0	0	0
71	1	27-08-2019	PRO	0	0	1	0	1	+1	0		23-05-2021	SAG		1		0	0	0
72	2	20-08-2019	DES	1	0	0	2	0	+2	3		06-04-2021	SAG			1	1	2	-1
73	3	31-08-2019	SAG	1	0	0	1	0	+1	3		26-02-2020	SAG	0	0	1	1	2	3
74	4	19-09-2019	FER	1	0	0	2	0	+2	3		31-08-2019	SAG	1	0	0	1	0	1
75	5	22-09-2019	SRC	1	0	0	4	1	+5	3		00-01-1900	SAG						0
76	6	20-10-2019	CAA	1	0	0	4	0	+4	3		00-01-1900	SAG				0	0	0
77	7	06-10-2019	CCU	1	0	0	1	0	+1	3		03-11-2021	SBE	1			5	0	5
78	8	13-10-2019	INT	1	0	0	1	0	+1	3		00-01-1900	SBE				0	0	0
79	9	23-10-2019	MAQ	1	0	0	3	1	+4	3		19-07-2021	SCC	1			4	0	4
80	10	27-10-2019	MAI	0	0	0	0	0	0	0		28-02-2021	SCC	1			2	1	1
81	11	03-11-2019	PET	0	0	1	0	2	+2	0		18-12-2019	SCC	0	1	0	1	1	2
82	12	23-11-2019	LIB	1	0	0	4	1	+5	3		00-01-1900	SCC				0	0	0
83	13	15-12-2019	ACA	1	0	0	3	0	+3	3		00-01-1900	SCC						0
84	14	18-12-2019	SCC	0	1	0	1	1	+2	1		00-01-1900	SCC	0	0	0	0	0	0
85	15	22-12-2019	WIL	1	0	0	2	0	+2	3		24-07-2021	SRC	1			4	2	2
86	16	19-01-2020	PRO	1	0	0	4	0	+4	3		07-03-2021	SRC	1			2	0	2
87	17	12-02-2020	DES	1	0	0	1	0	+1	3		16-02-2020	SRC	0	1	0	0	0	0
88	18	26-02-2020	SAG	0	0	1	1	2	+3	0		22-09-2019	SRC	1	0	0	4	1	5
89	19	08-02-2020	FER	0	1	0	1	1	+2	1		09-05-2021	WIL		1		1	1	0
90	20	16-02-2020	SRC	0	1	0	0	0	0	1		18-04-2021	WIL		1		2	2	0
91	21	23-02-2020	CAA	0	0	1	0	1	+1	0		22-12-2019	WIL	1	0	0	2	0	2
92	22	01-03-2020	CCU	1	0	0	2	1	+3	3		00-01-1900	WIL				0	0	0
93	23	08-03-2020	INT	1	0	0	2	0	+2	3		00-01-1900	WIL						0
94	24	14-03-2020	MAQ	1	0	0	3	1	+4	3		00-01-1900	WIL	0	0	0	0	0	0
95	25	00-01-1900	MAI	0	0	0	0	0	0	0		00-01-1900	0	0	0	0	0	0	0
96	26	00-01-1900	PET	0	0	0	0	0	0	0		00-01-1900	0	0	0	0	0	0	0
97	27	00-01-1900	LIB	0	0	0	0	0	0	0		00-01-1900	0	0	0	0	0	0	0
98	28	00-01-1900	ACA	0	0	0	0	0	0	0		00-01-1900	0	0	0	0	0	0	0
99	29	00-01-1900	SCC	0	0	0	0	0	0	0		00-01-1900	0	0	0	0	0	0	0
100	30	00-01-1900	WIL	0	0	0	0	0	0	0		00-01-1900	0	0	0	0	0	0	0
Seasons

Cell Formulas
Range	Formula
L5:S100	L5	=SORTBY(CHOOSE({1,2,3,4,5,6,7,8},$B$5:$B$100,$C$5:$C$100,$D$5:$D$100,$E$5:$E$100,$F$5:$F$100,$G$5:$G$100,$H$5:$H$100,$I$5:$I$100,$J$5:$J$100),$C$5:$C$100,1,$B$5:$B$100,-1)
Dynamic array formulas.

 

[Fluff]

How about

=SORT(FILTER(B5:J100,C5:C100="ACA"),1,-1)

You could also simplify your formula like

=SORT(B5:J100,{2,1},{1,-1})

 

[valmir]

Thanks a lot once again. I knew it would be something like that but from theory to actually putting into practice goes a long way!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[valmir]

Hi Fluff! I was wondering if there is an "opposite" function to this formula, meaning, instead of including all matching "ACA" values, in this case, that would be including all matching values except those with ACA?

 

[Fluff]

Yup, just change the ="ACA" to <>"ACA"

 

[valmir]

Worked perfectly! Thanks! ???

 

[Fluff]

My pleasure.