Topic 07
FILTER
FILTER works differently from every other lookup function. Instead of a criteria — a single value to match — it uses a condition: a logical expression that returns TRUE or FALSE for every row. Every row where the condition is TRUE comes back in the result. This is what makes FILTER uniquely powerful.
Recall from Topic 05
VLOOKUP and XLOOKUP always return only the first match — duplicates are silently ignored. FILTER is the answer to that limitation. It returns every row that satisfies the condition, not just the first one.
Condition vs Criteria — A Fundamental Difference
Every other lookup function takes a criteria — a single value to match against. FILTER takes a condition — a full logical expression. This one difference unlocks every comparison operator.
VLOOKUP · XLOOKUP · COUNTIF · SUMIF
Criteria — value only, always means "equal"
You provide a value. The function checks where the data equals that value. You cannot ask "greater than" or "not equal to" — it is always an equality check.
= only
FILTER
Condition — a full logical expression
You write a condition that returns TRUE or FALSE for every row. Any logical operator works — equal, not equal, greater than, less than, or any combination.
=
<>
>
<
>=
<=
FILTER Syntax
= FILTER(array, include, [if_empty])
array
The range or table to return rows from. Using a named Table (e.g.
table1) returns all columns automatically and stays dynamic as rows are added.
include
The condition. A logical expression written against a column of the table —
table1[Building]="Doha". Excel evaluates this for every row and returns rows where it is TRUE. This is not a criteria value — it is a full logical statement.
[if_empty]
Optional. What to return when no rows match. If omitted, Excel returns a
#CALC! error — covered at the end of this topic.
Examples
Basic
Return all staff in the Doha building
The condition checks every row: is this row's Building equal to "Doha"? All rows where the answer is TRUE are returned — however many that is.
= FILTER(table1, table1[Building] = "Doha")
This is a spill formula
The result spills automatically into as many rows as there are Doha staff. You type the formula in one cell — Excel fills the rest. If the data changes, the result updates instantly.
Power — operators VLOOKUP can't do
Use any comparison operator
Because the second argument is a condition — not just a value — you can use any operator. This is something no other lookup function can do.
— staff NOT in Doha
= FILTER(table1, table1[Building] <> "Doha")
= FILTER(table1, table1[Building] <> "Doha")
— staff with salary above 50,000
= FILTER(table1, table1[Salary] > 50000)
= FILTER(table1, table1[Salary] > 50000)
— staff with 30 or more years of service
= FILTER(table1, table1[Years] >= 30)
= FILTER(table1, table1[Years] >= 30)
Multiple Conditions — * for AND, + for OR
To combine conditions, wrap each one in parentheses and join them with * (AND) or + (OR). These are not the standard AND() and OR() functions — they are arithmetic operators applied to the TRUE/FALSE arrays each condition produces.
*
AND — both must be true
Multiplies TRUE(1) × TRUE(1) = 1. Any FALSE(0) makes the product 0 — the row is excluded.
TRUE×TRUE=✓ included
TRUE×FALSE=✗ excluded
FALSE×TRUE=✗ excluded
FALSE×FALSE=✗ excluded
+
OR — at least one must be true
Adds TRUE(1) + TRUE(1) ≥ 1. The row is included if the sum is greater than zero — i.e. at least one condition is TRUE.
TRUE+TRUE=✓ included
TRUE+FALSE=✓ included
FALSE+TRUE=✓ included
FALSE+FALSE=✗ excluded
* — AND
Doha building AND Full Time status
Both conditions must be true. Wrapping each in parentheses keeps the logic clear.
= FILTER(table1, (table1[Building] = "Doha") * (table1[Status] = "Full Time"))
+ — OR
Doha building OR Lusail building
Either condition being true is enough to include the row.
= FILTER(table1, (table1[Building] = "Doha") + (table1[Building] = "Lusail"))
* combined
Doha building AND salary above 40,000
Mix equality and comparison operators freely — any combination works.
= FILTER(table1, (table1[Building] = "Doha") * (table1[Salary] > 40000))
The #CALC! Error — No Results Found
FILTER-specific error
#CALC!
When no rows match the condition, FILTER has nothing to return — and it shows
Think of it like
#CALC!. This is not a formula error; the formula is perfectly correct. It is simply telling you the result set is empty.Think of it like
#N/A in VLOOKUP — it means "I couldn't find what you asked for." The difference is that FILTER returns #CALC! specifically when a valid condition produces zero results.
Fix — use the optional 3rd argument:
The 3rd argument is what FILTER returns instead of the error when the result is empty. It can be any text, number, or even another formula.
=FILTER(table1, table1[Building]="XYZ", "No staff found")The 3rd argument is what FILTER returns instead of the error when the result is empty. It can be any text, number, or even another formula.
Training Exercise
Open StaffData.xlsx. Make sure it is already set up as a table named table1 from Topic 02. On a new sheet or in an empty area, practice each formula below. Change the values in the conditions and observe how the result updates automatically.
- All staff in the Doha building → single condition with
= - All staff with salary above 50,000 → single condition with
> - All Full Time staff in Doha → two conditions with
* - All staff in Doha or Lusail → two conditions with
+ - Try a building name that does not exist → observe the
#CALC!error, then add the 3rd argument to handle it
Quick verification
From Topic 02 we know: Doha has 199 staff. Your FILTER result for
table1[Building]="Doha" should return exactly 199 rows. Use COUNTA on the spill range to verify: =COUNTA(A1#) where A1 is your FILTER formula cell.