XLOOKUP returns the first match as a single value. FILTER returns every matching row as a spilled array. They are not really competitors — they answer different questions: "what is the value for this key?" versus "show me all rows that qualify." Below: 13 capabilities scored honestly, 7 behavioral differences measured live in the same engine, and a guide to picking the right one.
The choice is about cardinality, not quality. XLOOKUP is a lookup — it finds one key and returns one value (or one row). FILTER is a filter — it returns all rows that meet a condition, spilling into adjacent cells. Use XLOOKUP to enrich a record; use FILTER to extract a subset of a table.
Every row is a real capability difference. The split is cardinality: XLOOKUP yields one value, FILTER yields a spilled array of all matches.
| Capability | XLOOKUP | FILTER | Why it matters |
|---|---|---|---|
| Number of results | ✓ First match only | ✓ All matches | The defining difference. |
| Result shape | ✓ Single value | ✓ Spilled array | FILTER fills adjacent cells. |
| No-match handling | ✓ if_not_found arg | ✓ if_empty arg | FILTER errors without if_empty. |
| AND criteria | ◐ Boolean array trick | ✓ Native (cond1)*(cond2) | FILTER is built for it. |
| OR criteria | ✗ Awkward | ✓ Native (cond1)+(cond2) | FILTER's home turf. |
| Sort the results | ✗ Not built in | ✓ Wrap in SORT() | Composable array pipeline. |
| Return multiple columns | ◐ One return_array | ✓ Whole rows | FILTER can return A2:C8. |
| Approximate match | ✓ match_mode ±1 | ✗ Exact filter only | XLOOKUP does next-smaller/larger. |
| Search direction | ✓ search_mode (last→first) | ✓ Always all rows | XLOOKUP can find the last match. |
| Spill / #SPILL! risk | ✓ None | ◐ Needs empty space | Blocked spill range errors. |
| Feed other array functions | ◐ Returns a value | ✓ Returns an array | FILTER → SORT/UNIQUE/INDEX. |
| Case sensitivity | ✓ Insensitive | ◐ Depends on criterion | Use EXACT for case-sensitive FILTER. |
| Excel version | ✓ 365 / 2021+ | ✓ 365 / 2021+ | Both are modern functions. |
XLOOKUP looks up one key and returns one value, with optional fallback and match/search modes. FILTER takes an array and a boolean include condition, returning every row where it is TRUE.
Type your own XLOOKUP and FILTER formulas against the live grid. FILTER spills its results down the column.
These are not feature-list claims. Each pair below was evaluated on the identical dataset in the HyperFormula 3.5 engine, and the outputs are baked from that run. Reproduce them in the playground above.
=XLOOKUP("East",A2:A8,B2:B8)=FILTER(B2:B8,A2:A8="East")=XLOOKUP("East",A2:A8,D2:D8)=FILTER(D2:D8,A2:A8="East")=XLOOKUP("South",A2:A8,B2:B8,"None")=FILTER(B2:B8,A2:A8="South")=XLOOKUP(1,(A2:A8="East")*(B2:B8="Widget"),D2:D8)=FILTER(D2:D8,(A2:A8="East")*(B2:B8="Widget"))=XLOOKUP("East",A2:A8,D2:D8)=FILTER(D2:D8,(A2:A8="East")+(A2:A8="West"))=XLOOKUP(10,C2:C8,B2:B8)=FILTER(B2:B8,C2:C8>5)=XLOOKUP("East",A2:A8,B2:B8)=SORT(FILTER(B2:B8,A2:A8="East"))Same dataset, same engine, same machine — relative cost only. XLOOKUP stops at the first match; FILTER scans every row, so it does more work as the table grows.
| Table size | XLOOKUP | FILTER |
|---|---|---|
| 1,000 rows × 200 lookups | 175.56 ms | 246.08 ms |
| 5,000 rows × 200 lookups | 1983.04 ms | 2410.01 ms |
| 10,000 rows × 200 lookups | 7129.45 ms | 8159.71 ms |
Match your situation to a recommendation. The rule of thumb: one value for a key → XLOOKUP; a subset of rows → FILTER.
| Situation | Use | Why |
|---|---|---|
| Looking up one value for a key (price, name, status) | XLOOKUP | Returns a single value, no spill. |
| Extracting every row that meets a condition | FILTER | Returns all matches as a spilled array. |
| Filtering by two or more conditions (AND) | FILTER | Native (cond1)*(cond2) boolean logic. |
| Filtering by this OR that | FILTER | Native (cond1)+(cond2); XLOOKUP cannot. |
| You need the results sorted or deduplicated | FILTER | Wrap in SORT() or UNIQUE(). |
| You need an approximate (next-smaller/larger) match | XLOOKUP | match_mode ±1; FILTER is exact-only. |
| You need the last match, not the first | XLOOKUP | search_mode -1 searches last-to-first. |
| Enriching one record in a larger formula | XLOOKUP | A scalar drops into any expression; an array may spill. |
Tasks where you reached for XLOOKUP but actually wanted every match. FILTER returns the full subset instead of the first row.
| Task | XLOOKUP / old way | FILTER | What changed |
|---|---|---|---|
| First East product | =XLOOKUP("East",A2:A8,B2:B8) | =FILTER(B2:B8,A2:A8="East") | FILTER returns all 4 East products, not just Widget. |
| All East amounts | =XLOOKUP("East",A2:A8,C2:C8) | =FILTER(C2:C8,A2:A8="East") | XLOOKUP gives only 1000; FILTER gives the full list. |
| Two conditions (AND) | =XLOOKUP(1,(A2:A8="East")*(B2:B8="Widget"),C2:C8) | =FILTER(C2:C8,(A2:A8="East")*(B2:B8="Widget")) | FILTER returns every match (1000,700). |
| OR criteria | =XLOOKUP("East",A2:A8,C2:C8) | =FILTER(C2:C8,(A2:A8="East")+(A2:A8="West")) | FILTER handles OR natively; XLOOKUP cannot. |
| Sorted subset | =XLOOKUP("East",A2:A8,B2:B8) | =SORT(FILTER(B2:B8,A2:A8="East")) | FILTER composes with SORT; XLOOKUP does not sort. |