Show recipes based on selected ingredients - Google Sheets

Question

I've been trying to build a small database with Google Sheets for me, my wife, my friend and his partner, to make it quick and easy to search through our recipes from HelloFresh!

I've input all of the recipes, and I am able to query to show recipes we would like based on which meat/vegetable, and what main ingredient (pasta, rice etc).

The next thing I would like to do is have a list generate/filter based on what ingredients we have, in this case cells J6:J13. I would like the list to generate if any criteria is met. For example, if both Chicken Thigh and Beef Mince are selected, it will show all recipes that have chicken OR beef.

Would anyone be able to assist, please?

https://docs.google.com/spreadsheets/d/19Nrr5NurZ5SkLYYPg09dl_XJMe2gx7Ft2TFO4yNklKY/edit?usp=sharing

Answer 1

try:

=INDEX(IFERROR(UNIQUE(QUERY({Horizontal!B2:B, 
 FLATTEN(QUERY(TRANSPOSE(Horizontal!C2:P),,9^9))}, 
 "select Col1 where 1=1 and "&
 TEXTJOIN(" or ", 1, IF((J6:J15="")+(J6:J15="-"),,
 "Col2 contains '"&J6:J15&"'")))), "no selection"))