I am trying to create a query that populates information based on the user's selected drop downs. Query is all built and functional, and populates the items in the master data that match the selected drop down items (i.e. if dropdown A = Canada, B = Car, and C = Red it will populate all dealerships in Canada that are selling red cars.

My problem is that sometimes the "dealerships" are selling cars of all colors. Instead of entering each color individually into separate lines of the master sheet, I want my query to populate both the data matching the selected color AND anything that says "all". These are figurative scenarios as it's sensitive data, but here's what I have so far.

=QUERY(Master!B4:F,"SELECT * WHERE 1=1 "&IF(B4="All Regions",""," AND LOWER(C) = LOWER('"&B4&"') ")&IF(C4="All Vehicles",""," AND LOWER(D) = LOWER('"&C4&"') ")&IF(D4="All Colors",""," AND LOWER(E) = LOWER('"&D4&"') "),1)

I feel like I should be able to add something into the IF functions that will tell my sheet to populate data that has text that says "all" and whatever is in the dropdown cell... but I can't for the life of me get it to work.

1 Answer 1


I ended up answering my own question, and will post it here for anyone else who may run into the same issue. It turns out, my attempted solutions had syntax errors. Here's the final fix:

=QUERY(Master!B4:F,"SELECT * WHERE 1=1 "&IF(B4="All Regions",""," AND (LOWER(C) = LOWER('"&B4&"') OR LOWER(C) = LOWER('All')) ")&IF(C4="All Vehicles",""," AND (LOWER(D) = LOWER('"&C4&"') OR LOWER(D) = LOWER('All')) ")&IF(D4="All Colors",""," AND (LOWER(E) = LOWER('"&D4&"') OR LOWER(E) = LOWER('All')) "),1)

So now when my user selects the dropdown for the color "Blue" the report will populate dealerships selling Blue cars as well as dealerships selling cars of all colors.

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.