Filtering tables |
The easiest way to specify filters in the FILTER() function is to use the "Format > Search Expression" style for the cells containing filters.
If you want to access and construct search expressions directly e.g. by your formulas, you need to use them as plain text cells in the following form:
filter-type[sort-key+|-][n]{:|=}[filter-text]
where elements in [,] brackets are optional.
(optional) n is a digit representing (a sum of) options:
Thus n can be any number from 1 to 7.
Examples:
a:john
b:*smith
e:color
j:23 54
j:A M
j=$b$1 $b$2
k:15
n:
o:
a1+:john
b1-:*smith
e2+:color
a5:john
b2:*smith
e1:color
a1+5:john
b1-2:*smith
e2+1:color
Each cell in the filters parameter in the FILTER() function contains a single filter. That filters parameter can be also entered directly as an array within the FILTER() formula, like:
=Filter(B2:H100000, B1:H1, "", 0)
=Filter(B1:H100000, {"a:tom","n1+:","","a:new"}, "", 3, "no records")
=Filter(B1:H100000, {"a:tom";"";"";"a:new"}, "sheet1", 2)