Google Sheets – How to Search Multiple Columns for One Value

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-queryvlookup

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

Best Answer

Please use the following formulas

If your data is in Inventory!A11:G

enter image description here

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

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

enter image description here

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<>'' ")

enter image description here

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

Related Topic