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

Question

0	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
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					lookup_array	ret_array1	ret_array2
16			P-Line_B										2024-S	Product	Type
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	2024-S
26	Prod_G		shop4		120	24%		300	24%		t4		Prod_D	t3	600
27	Prod_G		shop4		135	35%		480	17%		t4		Prod_G	t4	600
28	Prod_H		shop2		935	27%		230	16%		t1		Prod_D	t3	500
29	Prod_I		shop3		134	18%		600	42%		t2		Prod_G	t4	480
30	Total				3029			5785					Prod_D	t3	300
31													Prod_G	t3	300
32													Prod_G	t3	300
33													Prod_G	t4	300
34													Prod_G	t3	150
35
36

---

Target

The final Output is Range M26:O34 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 and ret_array2 in Cell O16 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.

Therefore, summarized I need these two formulas:

Formula 1: Getting a descending list considering multiple criteria and only display certain positions from this list.
Fromula 2: Getting the corresponding data to it based on multiple flexible return_arrays.

---

Current Status

With help from previous questions I have been able to apply these two formulas:

1. Getting the required list
Answer from this question

=TOCOL(INDEX(SORT(
    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=""),
    FILTER(FILTER(A1:K31,(A1:K1=M11),""),a*b*c,"")),,-1),SEQUENCE(M21,,N21)),2)

This formula already works perfectly without any issue.

2. Getting the correspondig data
Answer from this question

SORT(FILTER(CHOOSECOL(A1:K30,
         XMATCH(M16,A1:K1),
         XMATCH(N16,A1:K1),
         XMATCH(O16,A1:K1)),
             (AND(M6:M8="")+COUNTIF(M6:M8,A1:A30))*
             (AND(N6:N8="")+COUNTIF(N6:N8,C1:C30))*
             (AND(O6:O8="")+COUNTIF(O6:O8,K1:K30)),""),,-1)

This formula almost gives me the correct result.

---

Question

Regarding the second formula I do not know how to apply SEQUENCE to it and how to order the columns so they match with the desired result displayed in the above table.

What I like about both formulas is that it is very easy to chain additional column criteria to them since in my orginal file I have even more. It would be great to keep this flexibility.

Do you have any idea how to solve the issue?

Answer 1

Try :

---

=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,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),{1,2,3}),2),3))

---

Depending on the language that you are using in your Excel the {1,2,3} might be entered as {1.2.3} to make it work.

For example in German Excel the formula works like this:

=LET(
    _a; ZÄHLENWENN(M6:M8;A1:A31)+UND(M6:M8="");
    _b; ZÄHLENWENN(N6:N8;C1:C31)+UND(N6:N8="");
    _c; ZÄHLENWENN(O6:O8;K1:K31)+UND(O6:O8="");
    _d; SPALTENWAHL(A1:K31;XVERGLEICH(HSTAPELN(N16;O16;M16);A1:K1));
    _e; SORTIEREN(FILTER(HSTAPELN(_d;FILTER(A1:K31;A1:K1=M11;""));_a*_b*_c;"");3;-1);
    ZEILENUMBRUCH(ZUSPALTE(INDEX(_e;SEQUENZ(M21;;N21);{1.2.3});2);3))

Alternatively replacing {1.2.3} with SEQUENZ(;3) also works:

=LET(
    _a; ZÄHLENWENN(M6:M8;A1:A31)+UND(M6:M8="");
    _b; ZÄHLENWENN(N6:N8;C1:C31)+UND(N6:N8="");
    _c; ZÄHLENWENN(O6:O8;K1:K31)+UND(O6:O8="");
    _d; SPALTENWAHL(A1:K31;XVERGLEICH(HSTAPELN(N16;O16;M16);A1:K1));
    _e; SORTIEREN(FILTER(HSTAPELN(_d;FILTER(A1:K31;A1:K1=M11;""));_a*_b*_c;"");3;-1);
    ZEILENUMBRUCH(ZUSPALTE(INDEX(_e;SEQUENZ(M21;;N21);SEQUENZ(;3));2);3))

---