Forum Discussion

cate6878's avatar
cate6878
Copper Contributor
Jun 06, 2025
Solved

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 th...
Formulas and Functions
  • m_tarler's avatar
    m_tarler
    Jun 09, 2025

    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:

     

cate6878's avatar
cate6878
Copper Contributor
Jun 09, 2025

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!

Resources