[SOLVED] Match and list values to a dynamic list including multiple column criteria

Match and list values to a dynamic list including multiple column criteria

0	A	C	E	F	H	I	K	L	N
1	Products	Shop	2023-S	2023-M	2024-S	2024-M
2
3	Product A	shop3	80	2%	500	22%	ColCrit1	ColCrit2	lookup_array
4	Product B	shop2	320	23%	180	60%	Product D	shop1	2024-s
5	Product B	shop1	90	8%	300	36%	Product G	shop3	return_array
6	Product C	shop3	500	15%	657	16%			Products
7	Product D	shop1	160	17%	500	15%
8	Product D	shop1	500	30%	600	8%	list	match
9	Product D	shop1	130	4%	300	4%	830	Product G
10	Product E	shop2	75	10%	450	10%	600	Product D
11	Product F	shop4	60	8%	370	4%	600	Product G
12	Product F	shop1	500	11%	850	4%	500	Product D
13	Product G	shop3	350	8%	150	15%	300	Product D
14	Product G	shop3	60	47%	600	7%	300	Product G
15	Product G	shop3	90	25%	830	35%	300	Product G
16	Product G	shop2	390	9%	325	13%	150	Product G
17	Product G	shop3	170	30%	300	9%
18	Product G	shop3	320	5%	300	12%
19	Product H	shop2	935	27%	230	16%
20	Product I	shop3	134	18%	600	42%

In Range K9:K16 all values from Column H which fullfill the column criteria in Range K4:K6 and Range L4:L6 are listed based on this formula:

=SORT(
    LET(
    a;COUNTIF(K4:K6;A1:A20)+AND(K4:K6="");
    b;COUNTIF(L4:L6;C1:C20)+AND(L4:L6="");
    FILTER(FILTER(A1:J20;(A1:J1=N4);"");a*b;""));;-1)

In Range L9:L16 I want to :

match the values accordingly to the results from Range K9:K16 and
the column entered as variable return_array in Cell N6.

The formulas from this question already come close to the result:

Option 1

=CHOOSECOLS(SORT(FILTER(CHOOSECOLS(A3:I20;XMATCH(N4;A1:I1);XMATCH(N6;A1:I1));COUNTIFS(K4:K6;A3:A20;L4:L6;C3:C20));;-1);2)

Option 2

=LET(
     a, K9:K16,
     b, A1:I1,
     c, A3:I20,
     d, XLOOKUP(N4,b,c,""),
     MAP(a,LAMBDA(α, @DROP(TOCOL(FILTER(IFS((d=α)*
                    (1-ISNA(XMATCH(A3:A20,K4:K6)+XMATCH(C3:C20,L4:L6))),
                    IF(c=0,x,c)),N6=b,""),3),
     COUNTIF(K9:α,α)-1))))

However, when for example Range K4:K6 or Range L4:L6 is empty and the list in Range K9:K16 is adjusted to it both formulas for Range L9:L16 return error #CALC!?

How do I need to modify them to make them adjusting based on the list in Range K4:K6?

Solution

I think you mean if empty than list all? In that case: =SORT(FILTER(CHOOSECOLS(A3:I20,XMATCH(N4,A1:I1),XMATCH(N6,A1:I1)),(AND(K4:K6="")+COUNTIF(K4:K6,A3:A20))*(AND(L4:L6="")+COUNTIF(L4:L6,C3:C20)),""),,-1)

Or:

=LET(x,LAMBDA(a,b,AND(a="")+COUNTIF(a,b)),
SORT(FILTER(CHOOSECOLS(A3:I20,XMATCH(N4,A1:I1),XMATCH(N6,A1:I1)),x(K4:K6,A3:A20)*x(L4:L6,C3:C20)),,-1))