Forum Discussion
Trying to search for multiple criteria and return multiple results
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:
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.