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.


  • 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
    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
    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
    Oct 25, 2020 at 23:28

1 Answer 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)

      "where Col1<>''")

Another option would be to use

           "where Col1<>'' ")

enter image description here

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

  • Please remember that you can also contribute to this community that helps you. As per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well. Oct 26, 2020 at 19:32
  • I'm so sorry about my late response, I was swamped with work & didn't find time to check this out properly until today. Your hard work helped me a lot! Thank you so much! I do have a question though, with getting ingredients based on dish, how do I expand the selection in A. I.e. how do I get more dishes into that list and have their ingredients show up? I tried changing a few things but they didn't work. I'm watching query tutorials as of now, hopefully to figure it out.
    – Genny
    Oct 29, 2020 at 3:52
  • 1
    Just an update on this, I added this to the query formula and it returned the multiple dishes as I wanted. It seems to automatically sort the selections in column B in the same order they are in the inventory. ' or A='"&$A4&"' or A='"&$A5&"' or A='"&$A6&"' I'm not familiar with how the query function works, so I'm not fully aware of exactly what I did here.
    – Genny
    Oct 29, 2020 at 16:58
  • Very well done @Genny. You are getting the way it works. That is great. Please do not hesitate to ask for help again if you get stuck :) Oct 29, 2020 at 17:17
  • thank you for all your help. :) I appreciate it a lot! :)
    – Genny
    Oct 30, 2020 at 0:21

Your Answer

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

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