| 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% | 2024-M | ||||||
| 6 | Product B | Shop2 | 500 | 4% | 70 | 4% | |||||||
| 7 | |||||||||||||
| 8 | 400 | selected data | |||||||||||
| 9 | Product C | Shop2 | 160 | 10% | 245 | 10% | 400 | Product B | |||||
| 10 | Product D | Shop1 | 500 | 8% | 130 | 4% | 400 | Product E | |||||
| 11 | Product D | Shop4 | 130 | 11% | 130 | 4% | 70 | Product B | |||||
| 12 | Product E | Shop2 | 75 | 8% | 650 | 15% | 520 | Product H | |||||
| 13 | Product E | Shop1 | 60 | 47% | 90 | 7% | 130 | Product D | |||||
| 14 | Product E | Shop4 | 500 | 25% | 400 | 35% | 90 | Product E | |||||
| 15 | 130 | Product D | |||||||||||
| 16 | 70 | 130 | Product F | ||||||||||
| 17 | 70 | Product H | |||||||||||
| 18 | Product E | Shop3 | 350 | 9% | 140 | 13% | |||||||
| 19 | Product F | Shop2 | 60 | 30% | 130 | 9% | |||||||
| 20 | Product G | Shop2 | 90 | 5% | 370 | 12% | |||||||
| 21 | Product H | Shop1 | 390 | 27% | 70 | 16% | |||||||
| 22 | Product H | Shop2 | 70 | 18% | 520 | 42% |
In Range M9:M17 I want to get the corresponding data based on the values in Range K9:17.
This works both when applying the solutions from this question and this question:
Option 1 (Without flexible return_array)
=LET(
_Data, A3:I22,
_Col, XLOOKUP(M3,A1:I1,_Data,""),
_SelectedData, K9:K17,
_Fx, LAMBDA(r,s, MAP(r,LAMBDA(c,COUNTIF(s:c,c)/10+c))),
TOCOL(IF(_Fx(_Col,TAKE(_Col,1))=TOROW(_Fx(_SelectedData,
TAKE(_SelectedData,1))),TAKE(_Data,,1),NA()),3,1))
Option 2 (With flexible return_array –> Cell M3)
=IFERROR(LET(
a, K9:K17,
b, A1:I1,
c, A3:I22,
d, XLOOKUP(M3,b,c,""),
MAP(a,LAMBDA(e, @DROP(TOCOL(FILTER(IFS(d=e,c),M6=b),3),
COUNTIF(K9:e,e)-1)))),"")
However, as you can see in my data table above it can happen that there are empty rows in between the data which contain values (Cell H8 and Cell H16) that are also listed in Range K9:K17.
In this case both formulas mentioned above return a 0 in between the list in Range M9:M17.
In order to avoid this issue there must be somehow a condition added to the formula which says that all rows which do not have value in Column A should be ignored.
Do you know how to modify the formula to get the expected outcome?