1

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

3
  • Yes, it is possible. Please share a link to a sample sheet containing adequate and realistic data in one sheet (as shown in the image in your post) and the search setup you want to use in another sheet, manually filled in for now. Be sure to set the Share permission on the link to "Anyone with the link can edit." This will allow contributors here to offer efficient, effective and tested solutions. As things stand, you're asking contributors here to put in the work to recreate your data in their own Drive and then share it back to you; and that decreases the likelihood that you'll receive help.
    – Erik Tyler
    Commented Oct 25, 2020 at 6:08
  • @ErikTyler Thank you for that suggestion. I didn't think of it. Here's the link. docs.google.com/spreadsheets/d/…
    – Genny
    Commented Oct 25, 2020 at 14:09
  • Looks like another contributor saw this and helped you with formulas before I was able. See if those suggestions work for you.
    – Erik Tyler
    Commented Oct 25, 2020 at 23:28

1 Answer 1

1

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.