[SOLVED] Match and list values based on values listed in another column

Match and list values based on values listed in another column

0	A	C	E	F	H	I	K	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?

Solution

Quick fix to the existing formula (I believe ultimately you would need flexible return_array) :

=LET(
     _A, K6:K14,
     _B, A1:I1,
     _C, A3:I22,
     _D, XLOOKUP(M3,_B,_C,""),
     MAP(_A,LAMBDA(α, @DROP(TOCOL(FILTER(IFS(_D=α,IF(_C=0,x,_C)),M5=_B),3),
     COUNTIF(K6:α,α)-1))))