I am creating a drop down filter system using QUERY function for a grant database. Since you cannot do multi-select drop downs, we had to make 3 columns of “focus areas” for each grant opportunity. I want to be able to select a focus area from the drop down and need to be able to search and match across those three columns to pull matching grants. Here is the formula:
=QUERY(‘FUNDING DATABASE’!A1:K, “SELECT * WHERE 1=1″&IF(B4=”All”,””,” AND LOWER(F) = LOWER(‘”&B4&”‘) “)&IF(C4=”All Types”,””,” AND LOWER(E) = LOWER(‘”&C4&”‘) “)&IF(D4=””,””,” AND LOWER(B) = LOWER(‘”&D4&”‘) “),1)
The bold is where I need to make this change. I want it to search not just in column F, but columns F, G, or H is there is a match that = B4
I have tried nestling in the OR function, I have tried concantonating multiple queries and can’t get it to work
Loren Halili is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Use the OR keyword.
=QUERY('FUNDING DATABASE'!A1:K, "WHERE 1=1"&
IF(B4="All",," AND (LOWER(F) = LOWER('"&B4&"') OR
LOWER(G) = LOWER('"&B4&"') OR
LOWER(H) = LOWER('"&B4&"'))")&
IF(C4="All Types",," AND LOWER(E) = LOWER('"&C4&"') ")&
IF(D4="",," AND LOWER(B) = LOWER('"&D4&"') "),1)