Monday, October 29, 2012

Google Spreadsheet advanced query formula


hiya, i'd like to be able to filter a list using logical OR on a series of terms i type into a cell. I guess i can do this with the SPLIT and ARRAYFORMULA functions, but how? Here is an example search with a single search term.

Would be great if someone could give me the correct arrayformula to search multiple terms. Feel free to copy & edit the doc.



Thanks, though this is not what i'm looking for (but i would use a query instead if the google sytnax allowed creation of new fields or complex where clauses, but it doesn't seem to)

I want to filter the list in sheet "datasheet" according to a series of search terms. So for example entering "red, green" into cell B1 of sheet "filtersheet" would return 5 items.



Suppose you have following data in 'datasheet':

Then put the following formula in Cell A3 of 'filtersheet':

=query(datasheet!A1:Z;"select * where B contains '" & join("' or B contains '";arrayformula(trim(split(B1;",")))) &"'")

Then you will get the following output on the 'filtersheet':

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private or confidential projects:

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: 


1 comment:

  1. That's brilliant, thankyou. Very neat and clear.