| 0 | A | B | C | D | E | F | G | H | I | J | K | L | M |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Products | Shop | 2023-S | 2023-M | 2024-S | 2024-M | |||||||
| 2 | lookup_array | ||||||||||||
| 3 | Product A | Shop3 | 80 | 2% | 120 | 22% | 2024-S | ||||||
| 4 | Product B | Shop1 | 320 | 17% | 400 | 15% | return_array | ||||||
| 5 | Product B | Shop3 | 90 | 30% | 750 | 8% | selected data | 2024-M | |||||
| 6 | Product B | Shop2 | 500 | 4% | 70 | 4% | 400 | 15% | |||||
| 7 | Product C | Shop2 | 160 | 10% | 245 | 10% | 400 | 35% | |||||
| 8 | Product D | Shop1 | 500 | 8% | 130 | 4% | 70 | 4% | |||||
| 9 | Product D | Shop4 | 130 | 11% | 130 | 4% | 520 | 42% | |||||
| 10 | Product E | Shop2 | 75 | 8% | 650 | 15% | 130 | 4% | |||||
| 11 | Product E | Shop1 | 60 | 47% | 90 | 7% | 90 | 7% | |||||
| 12 | Product E | Shop4 | 500 | 25% | 400 | 35% | 130 | 4% | |||||
| 13 | Product E | Shop3 | 350 | 9% | 140 | 13% | 130 | 9% | |||||
| 14 | Product F | Shop2 | 60 | 30% | 130 | 9% | 70 | 16% | |||||
| 15 | Product G | Shop2 | 90 | 5% | 370 | 12% | |||||||
| 16 | Product H | Shop1 | 390 | 27% | 70 | 16% | |||||||
| 17 | Product H | Shop2 | 70 | 18% | 520 | 42% |
In Range M6:M14 I want to get the corresponding data based on the values in Range K6:14.
However, I want to have the flexibility that
- the
lookup_arrayis used based on the input inCell M3and - the
return_arrayis used based on the input inCell M5. For example if I would switch from2024-MtoProductthe corresponding products fromColumn Ashould be listed.
With reference to the answer in this question I am looking for something like this:
=LET(
_A, K6:K14,
_B, >> flexible based on input in Cell M3 <<,
_RollingA, MAP(_A,LAMBDA(α,COUNTIF(α:K6,α))),
_RollingB, MAP(_B,LAMBDA(α,COUNTIF(α:H3,α))),
XLOOKUP(_A&"|"&_RollingA,_B&"|"&_RollingB,XLOOKUP(M5,A1:I1,A3:I17)))
Note:
Values in the lookup_array are not unique. In this example 130, 400 and 70. Therefore, in case the frequency of the values in Range K6:14 does not match with the frequency of the values in the return_array then in Range M6:M14 they should be matched to the values in Range K6:14 in a descending order. (The example formula above already handles it this way).
What formula do I need to make the outcome in Range M6:M14 work based on a flexible lookup_array and return_array?