Search multiple columns for one value

Question

This is roughly what my table looks like:

A            B               C               D               E               F

Name    |Ingredient 1   |Ingredient 2   |Ingredient 3   |Ingredient 4   |Ingredient 5

Dish 1  |flour          |tomato         |potato         |eggs           |wine

Dish 2  |eggs           |onion          |capsicum       |tomato         |pasta

Dish 3  |flour          |eggs           |dill           |chicken        |pork

What I want is to search for the ingredient, and get a result of all the dishes that contain that particular ingredient or even highlight the dish name. Each ingredient is in a different column and not in any sorted order. E.g. suppose I enter in another cell somewhere (or in another sheet) the word tomato, then all the dishes that contain that ingredient should show up. Is that possible with google sheets? This will be a constantly growing table.

https://docs.google.com/spreadsheets/d/1rlATwRPTkofEg0rB5-GSwwBpQDa7ZOoeNvhs0xPOsKA/edit?usp=sharing

Answer 1

Please use the following formulas

If your data is in Inventory!A11:G

for finding the ingredients based on dish, place in cell Search for Dish!B2

=QUERY(Inventory!A11:G,"where A='"&A3&"'",1)

for finding the dish based on ingredient, place in cell Search with ingredient!B1 either of the following formulas (these formulas are a bit tricky)

=QUERY(arrayformula(IF(MMULT(IFERROR(SEARCH(A1,Inventory!B12:G))+0, 
         TRANSPOSE(LEN(Inventory!B12:G12)*0+1)),Inventory!A12:A,"")), 
      "where Col1<>''")

Another option would be to use

=QUERY(ArrayFormula(IF(IFERROR( 
     REGEXEXTRACT(Inventory!B12:B&Inventory!C12:C&Inventory!D12:D&Inventory!E12:E&Inventory!F12:F&Inventory!g12:g,A1))<>A1,,Inventory!A12:A)), 
           "where Col1<>'' ")

I have created 2 new sheets in your spreadsheet. Please have a look at them