TABLEFILTER
Filters rows from a table (array-of-arrays) using multiple column-specific criteria with AND logic. Unlike FILTER, which applies a single condition to entire rows, TABLEFILTER lets you specify different criteria for different columns — ideal for dashboards, reports, and multi-dimensional data queries.
Syntax
TABLEFILTER(table, colIndex1, query1, colIndex2, query2, ...)table (Table | Array): The source table or array-of-arrays.
colIndex (Number): Zero-based column index to filter on.
query (String | Function | Array):
String: Supports comparison operators (
>,>=,<,<=,<>) and wildcards (*,?).Function: Custom filter logic (
function(value) -> boolean).Array: List of allowed values.
Returns: Table – A new table containing only rows that match all criteria (AND logic). Returns empty table if no matches.
Requires at least 3 arguments: the table plus one column/query pair. Add more pairs for additional AND conditions.
Examples
Simple Example
// Filter sales table: product = "apple" AND amount > 1000
TABLEFILTER(TABLE1, 1, "apple", 3, ">1000")
// Multiple wildcards: region starts with "N" AND status is "active" or "pending"
TABLEFILTER(TABLE2, 0, "N*", 2, ARRAY("active", "pending"))Business Example: High-Value Regional Sales
Extract all sales from the EU region over €5000 for quarterly reporting:
TABLEFILTER(transactions, 2, "EU", 4, ">5000")Returns only EU transactions exceeding the threshold — ready for pivot tables or executive summaries.
String Query Operators
>,>=,<,<=— Numeric/date comparisons<>— Not equal*— Wildcard (any characters)?— Single character wildcardExact match — No operator needed
All criteria use AND logic. For OR conditions, use multiple TABLEFILTER calls and combine results with array functions.
Multi-select dropdown cells
In a table column with Multi select turned on, each cell holds a list of option labels, and TABLEFILTER matches your query against every label in that list. The match is the exact option label, not a substring: a query of "Retail" keeps rows whose cell contains Retail on its own or together with other options, such as ["Retail", "Fintech"], but not "Retailing". A cell with no selection matches no label query.
// Keeps rows whose Industry column contains the exact label Retail,
// whether the cell holds ["Retail"] or ["Retail", "Fintech"]
TABLEFILTER(LEADS, 1, "Retail")The <> operator works the other way around: "<>Retail" keeps only rows whose selections do not contain the exact label Retail, so ["Wholesale"] and ["Retailing"] match while ["Retail", "Fintech"] does not.
Cells that hold a single value, as in a single-select dropdown column, are matched as one value with the usual operators and wildcards.
Common Mistakes
Column indexes are zero-based (first column is 0, not 1).
Must provide an even number of column/query pairs after the table argument.
String queries are case-sensitive by default.
Numeric comparisons in strings (e.g.,
">100") parse numbers automatically — don't mix with text.
For single-condition filtering or non-tabular arrays, use FILTER instead — it's simpler and more flexible for general-purpose filtering.
Combining with Other Functions
Chain with TABLESORT for sorted filtered results:
TABLESORT(TABLEFILTER(data, 0, ">100"), 1, "desc")Count matching rows:
LEN(TABLEFILTER(orders, 2, "Shipped"))