Filtering a Non-Unique Data Set in Google Sheets-CodePudding

I have a list of recipe, and I also have a list of sold menu. Is there any way that can filter the recipe (can be with query, or not) by looking at the menu (that can repeat more than once)?

CodePudding user response：

In case you have unmatching amount of items per menu (I added two extra elements to tenderloin), you can try this other option so you don't have empty spaces:

=TRANSPOSE(SPLIT(JOIN(",",BYROW(D2:D,LAMBDA(r,IF(r="",,JOIN(",",FILTER(A2:A, B2:B=r)))))),","))

CodePudding user response：

Another solution using REDUCE:

=REDUCE("recipe used",D2:D10,LAMBDA(a,c,{a;FILTER(A2:A10,B2:B10=c)}))