0

I have a long OR query function that I will be reusing many times in many contexts and that may change frequently. Following the principles of DRY design, I would like to write this query once and reference it in all the required places. So, for example, it would be nice to search for articles (all about fruit in this strange case...) that contain any of the following tags (assuming there is a column B for tags): Peaches, Apples, Oranges, Pears, Plums, Dragon Fruit, Mangoes, Kiwi.

Right now this would look something like

=QUERY('Fruit Articles'!A1:B1000,"SELECT * WHERE B = 'Peaches' OR B = 'Apples' OR B = 'Oranges' OR B = 'Pears' OR B = 'Plums' OR B = 'Dragon Fruit' OR B = 'Mangos' OR B = 'Kiwi'")

I would much prefer to have a column that a non-technical client can use to enter new search items and have the query variable automatically update. So if the user added "Pineapple" to column B then the new query would dynamically update to be

=QUERY('Fruit Articles'!A1:B1000,"SELECT * WHERE B = 'Peaches' OR B = 'Apples' OR B = 'Oranges' OR B = 'Pears' OR B = 'Plums' OR B = 'Dragon Fruit' OR B = 'Mangos' OR B = 'Kiwi' OR B = 'Pineapple'")

Obviously, this example is a bit silly but I have a particular solution in mind where this would be extremely useful.

1

Yes you can do it. There are a lot of approaches for this problem.

Example :

=QUERY(A:B,"SELECT * WHERE "&C1&C2")

Cell C1, C2 contains the rest of the query like

C1 B = 'Apples'

C2 OR B = 'Peaches'

You can build C1 and C2 with conditions such as

C1 =IF(D1<>"","B = '"&D1&"'","") Where D1 is your fruit's name

Then build more conditions for special cases when you have 0 or 1 item.

Here's a quick example

Edit : You maybe want to use CONTAINS instead of =which is case-sensitive.

0

If your QUERY is just for narrowing down the initial dataset via OR repetition, then it's better to use FILTER with a regex like:

=FILTER('Fruit Articles'!A1:B, 
 REGEXMATCH(LOWER('Fruit Articles'!B1:B), LOWER(TEXTJOIN("|", 1, D1:D))))

...where range D1:D will contain the list -

Peaches
Apples
Oranges
Pears
Plums
Dragon Fruit
Mangoes
Kiwi
...

0

spreadsheet demo

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.