| 0 | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | Brand A | Product 01 | 600 | Product 01 | US | online | |
| 2 | Brand A | Product 02 | 100 | Product 02 | NL | local shop | |
| 3 | Brand A | Product 02 | 300 | Product 03 | AT | online | |
| 4 | Brand B | Product 01 | 400 | Product 04 | FR | local shop | |
| 5 | Brand B | Product 03 | 500 | ||||
| 6 | Brand C | Product 02 | 200 | ||||
| 7 | Brand C | Product 02 | 800 | Brand C | |||
| 8 | Brand C | Product 04 | 900 | Product 02 | NL | local shop | |
| 9 | Brand C | Product 01 | 700 | Product 04 | FR | local shop | |
| 10 | Brand D | Product 03 | 250 | Product 01 | US | online | |
| 11 | Brand D | Product 03 | 460 | ||||
| 12 | Brand D | Product 04 | 690 |
In the table above I have two different lists:
List A = Range A1:C12
List B = Range E1:G4 (Info: The products in this list will be always unique)
In Range E8:E10 the List A gets filtered with this formula:
=UNIQUE(FILTER(B:B;A:A=E7))
All this works.
Now, in Range F8:G10 I want to add additional information to the filtered products from List B.
So far I am able to do this for the first row with this formula:
=DROP(FILTER(E1:G4;E1:E4=E8);;1)
How do I need to change the formula(s) to make it work for all rows?
>Solution :
If order does not matter we can nest the filters:
=FILTER(E1:G4,ISNUMBER(MATCH(E1:E4,UNIQUE(FILTER(B:B,A:A=E7)),0)))
If order matters then we use DROP(REDUCE())
=DROP(REDUCE("",UNIQUE(FILTER(B:B,A:A=E7)),LAMBDA(z,y,VSTACK(z,XLOOKUP(y,$E$1:$E$4,$E$1:$G$4)))),1)
