Google Spreadsheet advanced query formula

Question:

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

Alex

.......

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.

Thanks

Solution:


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:

http://igoogledrive.blogspot.com/p/paid-projects.html

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: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan,

iGoogleDrive