Filter and sort data with multiple column & row criteria and match corresponding data to it with flexible return_arrays

Question

0	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Product		Shop		2023-S	2023-M		2024-S	2024-M		Type
2
3								500
4			P-Line_A										Step1
5	Prod_A		shop3		80	2%		500	22%		t1		ColCrit1	ColCrit2	ColCrit3
6	Prod_B		shop2		320	23%		180	60%		t1		Prod_D	shop1	t3
7	Prod_B		shop1		90	8%		300	36%		t2		Prod_G	shop3	t4
8	Prod_C		shop3		500	15%		657	16%		t1			shop4
9	Prod_D		shop1		160	17%		500	15%		t3
10	Prod_D		shop1		500	30%		600	8%		t3		rowCrit
11	Prod_D		shop1		130	4%		300	4%		t3		2024-S
12	Total				1810			3037
13
14								600					Step2
15								300					l_array	r_array1	r_array2	r_array3
16			P-Line_B										2024-S	Product	Type	Shop
17	Prod_E		shop2		75	10%		450	10%		t1
18	Prod_F		shop4		60	8%		370	4%		t2
19	Prod_F		shop1		500	11%		850	4%		t2		Step3
20	Prod_G		shop3		350	8%		150	15%		t3		Rows	Start
21	Prod_G		shop3		60	47%		600	7%		t4		10	2
22	Prod_G		shop4		90	25%		830	35%		t4
23	Prod_G		shop2		390	9%		325	13%		t1
24	Prod_G		shop3		90	30%		300	9%		t3		Step4
25	Prod_G		shop4		90	5%		300	12%		t3		Product	Type	Shop	2024-S
26	Prod_G		shop4		120	24%		300	24%		t4		Prod_D	t3	shop1	600
27	Prod_G		shop4		135	35%		480	17%		t4		Prod_G	t4	shop3	600
28	Prod_H		shop2		935	27%		230	16%		t1		Prod_D	t3	shop1	500
29	Prod_I		shop3		134	18%		600	42%		t2		Prod_G	t4	shop4	480
30	Total				3029			5785					Prod_D	t3	shop1	300
31													Prod_G	t3	shop3	300
32													Prod_G	t3	shop4	300
33													Prod_G	t4	shop4	300
34													Prod_G	t3	shop3	150
35
36

---

Target

The final Output is Range M26:P34 which is a descending list based on the lookup_array in Cell M16 and multiple column and row filters (Range M6:M8, Range N6:O8, Range O6:O8 and Cell M11) and selected positions in the rank based on Cell M21 and Cell N21.

To get this list the following steps are required:
(You can also see the steps in the table in Column M so it is easier to follow the instructions)

1. Define multiple column criteria in Range M6:O6 and a row criteria in Cell M11. These criteria are all AND criteria. After filtering the list it should be sorted descending.
2. Based on Step1 you will have a descending list fulfilling the column and row criteria. For this list the corresponding values based on ret_array1 in Cell N16, ret_array2 in Cell O16 and ret_array3 in Cell P16 should be added.
   (Note: lookup_array = rowCrit --> Cell M16 = Cell M11)
3. From the list in Step2 only the values that are ranked in positions based on Cell M21 and Cell N21 should be displayed.
   In this case the 10 positions after the 1st position.
4. After Step1-3 in Step4 the final list should appear.

---

In comparison to the answer in this question I added an additional return_array3 in Cell P16.
I tried to implement this additional array to the formula like this:

=LET(
    _a; COUNTIF(M6:M8;A1:A31)+AND(M6:M8="");
    _b; COUNTIF(N6:N8;C1:C31)+AND(N6:N8="");
    _c; COUNTIF(O6:O8;K1:K31)+AND(O6:O8="");
    _d; CHOOSECOLS(A1:K31;XMATCH(HSTACK(N16;O16;P16;M16);A1:K1));
    _e; SORT(FILTER(HSTACK(_d;FILTER(A1:K31;A1:K1=M11;""));_a*_b*_c;"");3;-1);
    WRAPROWS(TOCOL(INDEX(_e;SEQUENCE(M21;;N21);SEQUENCE(;3));2);3))

However, now the values for 2024-S are not appearing anymore and the sorting is also different:

How do I need to modify the formula to make it work?

Answer 1

Try using the following, this is dynamic :

---

=LET(
 _a, COUNTIF(M6:M8,A1:A31)+AND(M6:M8=""),
 _b, COUNTIF(N6:N8,C1:C31)+AND(N6:N8=""),
 _c, COUNTIF(O6:O8,K1:K31)+AND(O6:O8=""),
 _d, TOROW(HSTACK(N16,O16,P16,M16),1),
 _e, CHOOSECOLS(A1:K31,XMATCH(_d,A1:K1)),
 _f, COLUMNS(_d),
 _g, SORT(FILTER(HSTACK(_e,FILTER(A1:K31,A1:K1=M11,"")),_a*_b*_c,""),_f+1,-1),
 WRAPROWS(TOCOL(INDEX(_g,SEQUENCE(M21,,N21),SEQUENCE(,_f)),2),_f))

---

With Headers as well:

---

=LET(
    _a, COUNTIF(M6:M8,A1:A31)+AND(M6:M8=""),
    _b, COUNTIF(N6:N8,C1:C31)+AND(N6:N8=""),
    _c, COUNTIF(O6:O8,K1:K31)+AND(O6:O8=""),
    _d, TOROW(HSTACK(N16,O16,P16,M16),1),
    _e, CHOOSECOLS(A1:K31,XMATCH(_d,A1:K1)),
    _f, COLUMNS(_d),
    _g, SORT(FILTER(HSTACK(_e,FILTER(A1:K31,A1:K1=M11,"")),_a*_b*_c,""),_f+1,-1),
    VSTACK(_d, WRAPROWS(TOCOL(INDEX(_g,SEQUENCE(M21,,N21),SEQUENCE(,_f)),2),_f)))