Forum Discussion
Trying to search for multiple criteria and return multiple results
Hi everyone! I've got a hang up that's easy in my head, but I just can't find the right formula to make it happen.
I work in catering and I'm trying to make a sheet that would allow us to search through our menu items for allergens more easily. I currently have a spreadsheet built that lists each menu item we offer, if it's vegetarian or vegan, if it's gluten free, and every allergen it contains (don't ask how long this took). I've attached a screenshot of the mock up version I've been messing around with. Ideally, I would like to be able to select the allergens I want to search for in the green drop down menu boxes, and have the full list of compliant items populate down from the gray cells. So far I've been able to pull a list of each allergen individually, but if a guest has a soy, dairy, nuts and legumes, and a shellfish allergy, it would be nice to be able to pull one cohesive list.
Maybe a further stretch, but my wish list for this sheet also involves some way to search "vegetarian" and have ALL of the vegetarian food populate, not just the vegetarian food that isn't vegan.
Here are the best potential solutions I've tried so far. If any of these can be tweaked and I missed it, please let me know:
- I can get XLOOKUP to search for multiple criteria with =XLOOKUP(1,(C3:C9=K6)*(D3:D9=K7),B4:B9,"none"), but then each drop down box had to be assigned a specific allergen, which kind of defeats the point of a drop down list. When I tried to give the lookup array a range that covered two columns, it yelled at me.
- FILTER worked fine when I was trying to search the entire sheet for one allergen at a time, but I couldn't find a way to make look for the crossover between two allergens when I wanted to refine the search.
- I tried: =FILTER(B4:G9,(C4:G9=K7),"none")
I'm open to any and every solution! I'll try to stay on top of this post if I can answer any clarifying questions. Appreciate the help!
FILTER is the way to go and to make FILTER do multiple criteria use (crit1)*(crit2) to AND then or (crit1)+(crit2) to OR them. Also the criteria should be a single row or column not a range. So
=FILTER(B4:G9,(C4:C9=K7)*(C4:C9=K8)*(D4:D9=K9),"none")
to have Veggie include Vegan you will have to add more logic but the easier way (IMHO) would be to remove the drop downs and make them checkboxes instead. i.e. J7 would say Vegetarian and K7 would have the checkbox and rows 8 and 9 would have Vegan and Gluten-Free. Then you can use:
=FILTER(B4:G9,IF(K7,(C4:C9=J7)+(C4:C9=J8),1)*IF(K8,(C4:C9=J8),1)*IF(K9,(D4:D9=J9),1),"none")
adding image of checkbox idea and attaching file:
4 Replies
Hi everyone! I've got a hang up that's easy in my head, but I just can't find the right formula to make it happen.
I work in catering and I'm trying to make a sheet that would allow us to search through our menu items for allergens more easily. I currently have a spreadsheet built that lists each menu item we offer, if it's vegetarian or vegan, if it's gluten free, and every allergen it contains (don't ask how long this took). I've attached a screenshot of the mock up version I've been messing around with. Ideally, I would like to be able to select the allergens I want to search for in the green drop down menu boxes, and have the full list of compliant items populate down from the gray cells. It reminds me a bit of looking through the menu at a place like Texas Roadhouse, where guests with specific preferences or dietary restrictions really appreciate being able to quickly see which dishes work for them without having to scan through everything manually. I found some helpful menu details on https://thetexasroadhousemenu.com/. So far I've been able to pull a list of each allergen individually, but if a guest has a soy, dairy, nuts and legumes, and a shellfish allergy, it would be nice to be able to pull one cohesive list.
Maybe a further stretch, but my wish list for this sheet also involves some way to search "vegetarian" and have ALL of the vegetarian food populate, not just the vegetarian food that isn't vegan.
I had the same question while trying to set up a spreadsheet for filtering menu items by multiple allergens. I could manage single criteria searches, but combining several at once was tricky. Thanks for solving the problem and glad to see others were working on the same challenge.Thanks everyone for the help!! I tested out a combination of suggestions, and here's a new picture here with the results:
For the "I want food that does not contain..." menu I used OliverScheurich​ 's suggestion of: =FILTER(B4:B9,NOT(BYROW(E4:G9,LAMBDA(r,SUM(COUNTIFS(J7:J11,r))))))
And for the "I want food that is..." menu I used m_tarler​ 's suggestion of combining checkboxes with: =FILTER(B4:G9,(IF(N7,(C4:C9=M7)+(C4:C9=M8),1)*IF(N8,(C4:C9=M8),1)*IF(N9,(D4:D9=M9),1)),"none")
Thanks again!
I do not understand what you need exactly. Look at the worksheet here, let me know if it is what you need and i will finish it up for you.
FILTER is the way to go and to make FILTER do multiple criteria use (crit1)*(crit2) to AND then or (crit1)+(crit2) to OR them. Also the criteria should be a single row or column not a range. So
=FILTER(B4:G9,(C4:C9=K7)*(C4:C9=K8)*(D4:D9=K9),"none")
to have Veggie include Vegan you will have to add more logic but the easier way (IMHO) would be to remove the drop downs and make them checkboxes instead. i.e. J7 would say Vegetarian and K7 would have the checkbox and rows 8 and 9 would have Vegan and Gluten-Free. Then you can use:
=FILTER(B4:G9,IF(K7,(C4:C9=J7)+(C4:C9=J8),1)*IF(K8,(C4:C9=J8),1)*IF(K9,(D4:D9=J9),1),"none")
adding image of checkbox idea and attaching file:
