| 0 | A | B | C | D | E | F | G | H | I | J | K | L | M | 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:K16and - the column entered as variable
return_arrayinCell 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?
3
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))
Maybe I have not understood clearly, if both the ColCrits are empty then why not return the outputs based on the lookup_array and return_array whilst matching the list accordingly, how does returning the whole column H helps in that scenario, is not deviating from the other conditions?:
=LET(
a, K9:K16,
b, A1:I1,
c, A3:I20,
d, XLOOKUP(N4,b,c,""),
e, (1-ISNA(XMATCH(A3:A20,K4:K6)*XMATCH(C3:C20,L4:L6))),
MAP(a,LAMBDA(α, @DROP(TOCOL(FILTER(IFS(IF(SUM(e)=0,(d=α),(d=α)*e),
IF(c=0,x,c)),N6=b,""),3),
COUNTIF(K9:α,α)-1))))
Update:
=LET(
a, K9:K16,
b, A1:I1,
c, A3:I20,
d, XLOOKUP(N4,b,c,""),
e, (1-ISNA(XMATCH(A3:A20&"|"&C3:C20,K4:K6&"|"&L4:L6))),
IFERROR(MAP(a,LAMBDA(α, @DROP(TOCOL(FILTER(IFS(IF(SUM(e)=0,(d=α),(d=α)*e),
IF(c=0,x,c)),N6=b,""),3),
COUNTIF(K9:α,α)-1))),""))
2