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.
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"))