Head-to-head · Lookup & Dynamic Arrays

XLOOKUP vs FILTER

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.

fx
XLOOKUP(…)  vs  FILTER(…)
13
capabilities compared
7
scenarios measured live
1st vs all
the core difference
▶ Try both live See the feature matrix
🏆Verdict: XLOOKUP for one value; FILTER for every matching row

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.

XLOOKUP strengths

  • Returns a single value — no spill, no #SPILL! risk
  • Built-in if_not_found, match_mode and search_mode
  • Ideal for key-based lookups and enriching one record

FILTER strengths

  • Returns every matching row, not just the first
  • Native AND (*) and OR (+) boolean criteria
  • Composes with SORT, UNIQUE and other array functions

Feature-by-feature matrix

Every row is a real capability difference. The split is cardinality: XLOOKUP yields one value, FILTER yields a spilled array of all matches.

CapabilityXLOOKUPFILTERWhy it matters
Number of results✓ First match only✓ All matchesThe defining difference.
Result shape✓ Single value✓ Spilled arrayFILTER fills adjacent cells.
No-match handling✓ if_not_found arg✓ if_empty argFILTER 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 rowsFILTER can return A2:C8.
Approximate match✓ match_mode ±1✗ Exact filter onlyXLOOKUP does next-smaller/larger.
Search direction✓ search_mode (last→first)✓ Always all rowsXLOOKUP can find the last match.
Spill / #SPILL! risk✓ None◐ Needs empty spaceBlocked spill range errors.
Feed other array functions◐ Returns a value✓ Returns an arrayFILTER → SORT/UNIQUE/INDEX.
Case sensitivity✓ Insensitive◐ Depends on criterionUse EXACT for case-sensitive FILTER.
Excel version✓ 365 / 2021+✓ 365 / 2021+Both are modern functions.

Syntax, side by side

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.

XLOOKUP

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
lookup_valueThe key to find.
lookup_arrayThe range to search for the key.
return_arrayThe range to return a value from.
if_not_foundOptional value returned when no match exists.
match_modeOptional: 0 exact, -1 next smaller, 1 next larger, 2 wildcard.
search_modeOptional: 1 first-to-last, -1 last-to-first.
=XLOOKUP("East", A2:A8, B2:B8)

FILTER

FILTER(array, include, [if_empty])
arrayThe range or array to filter.
includeA boolean array; rows where TRUE are kept. Combine with * (AND) and + (OR).
if_emptyOptional value returned when no row matches.
=FILTER(B2:B8, A2:A8="East")

Live head-to-head playground

Type your own XLOOKUP and FILTER formulas against the live grid. FILTER spills its results down the column.

A1Sample data — click any cell to edit; both formulas re-evaluate live

XLOOKUP

—

FILTER

—
HyperFormula 3.5 · live

Every difference, measured

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.

First match vs every match

Get the Product(s) for Region East. East appears four times.
XLOOKUP
=XLOOKUP("East",A2:A8,B2:B8)
Widget
FILTER
=FILTER(B2:B8,A2:A8="East")
Widget | Gadget | Widget | Gadget
XLOOKUP returns only the FIRST East product (Widget). FILTER spills ALL four East products (Widget | Gadget | Widget | Gadget). This is the core paradigm split: lookup-one vs filter-many.

One value vs a list of values

Get the Amount(s) for East.
XLOOKUP
=XLOOKUP("East",A2:A8,D2:D8)
1000
FILTER
=FILTER(D2:D8,A2:A8="East")
1000 | 1200 | 700 | 300
XLOOKUP returns 1000 (the first East row). FILTER returns the full list 1000 | 1200 | 700 | 300. Use FILTER when you need every matching record, not just one.

No match — both have a fallback

Look up Region South, which does not exist. XLOOKUP uses a fallback; FILTER is shown without one.
XLOOKUP
=XLOOKUP("South",A2:A8,B2:B8,"None")
None
FILTER
=FILTER(B2:B8,A2:A8="South")
#N/A
XLOOKUP's built-in if_not_found (4th argument) returns None. FILTER needs its own if_empty (3rd argument) to avoid an error; shown without it, FILTER errors (#N/A in this engine, #CALC! in Excel). Both functions can handle no-match, but with different arguments.

Multiple criteria (AND)

Get Amount(s) where Region is East AND Product is Widget.
XLOOKUP
=XLOOKUP(1,(A2:A8="East")*(B2:B8="Widget"),D2:D8)
1000
FILTER
=FILTER(D2:D8,(A2:A8="East")*(B2:B8="Widget"))
1000 | 700
XLOOKUP returns the first match (1000); FILTER returns both (1000 | 700). Multiply boolean arrays for AND in both.

OR criteria — FILTER's home turf

Get Amounts where Region is East OR West.
XLOOKUP
=XLOOKUP("East",A2:A8,D2:D8)
1000
FILTER
=FILTER(D2:D8,(A2:A8="East")+(A2:A8="West"))
1000 | 500 | 1200 | 450 | 700 | 300
XLOOKUP cannot express OR directly — it returns the first East (1000). FILTER adds boolean arrays with + for OR, returning all six East+West amounts (1000 | 500 | 1200 | 450 | 700 | 300).

Filter by a numeric threshold

Get Products where Qty is greater than 5.
XLOOKUP
=XLOOKUP(10,C2:C8,B2:B8)
Widget
FILTER
=FILTER(B2:B8,C2:C8>5)
Widget | Gadget | Widget | Widget
XLOOKUP finds the row with Qty exactly 10 (Widget). FILTER returns every product with Qty>5 (Widget | Gadget | Widget | Gadget). Threshold filtering is a FILTER job.

Compose with SORT

Get the East products, sorted alphabetically.
XLOOKUP
=XLOOKUP("East",A2:A8,B2:B8)
Widget
FILTER
=SORT(FILTER(B2:B8,A2:A8="East"))
Gadget | Gadget | Widget | Widget
XLOOKUP returns one unsorted value (Widget). FILTER composes with SORT to return a sorted list (Gadget | Gadget | Widget | Widget). Dynamic-array functions stack; XLOOKUP does not spill.

Engine benchmark

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 sizeXLOOKUPFILTER
1,000 rows × 200 lookups175.56 ms246.08 ms
5,000 rows × 200 lookups1983.04 ms2410.01 ms
10,000 rows × 200 lookups7129.45 ms8159.71 ms
Measured in the HyperFormula 3.5 engine on this page, not in Microsoft Excel. Absolute times are engine-specific; use only as a relative signal. Real Excel performance depends on version, data layout and calculation chain.

Which one should you use?

Match your situation to a recommendation. The rule of thumb: one value for a key → XLOOKUP; a subset of rows → FILTER.

SituationUseWhy
Looking up one value for a key (price, name, status)XLOOKUPReturns a single value, no spill.
Extracting every row that meets a conditionFILTERReturns all matches as a spilled array.
Filtering by two or more conditions (AND)FILTERNative (cond1)*(cond2) boolean logic.
Filtering by this OR thatFILTERNative (cond1)+(cond2); XLOOKUP cannot.
You need the results sorted or deduplicatedFILTERWrap in SORT() or UNIQUE().
You need an approximate (next-smaller/larger) matchXLOOKUPmatch_mode ±1; FILTER is exact-only.
You need the last match, not the firstXLOOKUPsearch_mode -1 searches last-to-first.
Enriching one record in a larger formulaXLOOKUPA scalar drops into any expression; an array may spill.

From XLOOKUP to FILTER migration

Tasks where you reached for XLOOKUP but actually wanted every match. FILTER returns the full subset instead of the first row.

TaskXLOOKUP / old wayFILTERWhat 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.

Frequently asked questions

What is the difference between XLOOKUP and FILTER?
XLOOKUP returns the first match for a key as a single value. FILTER returns every row that meets a condition as a spilled array. XLOOKUP is a lookup; FILTER is a subset extractor.
Can XLOOKUP return multiple rows?
Not by default — it returns the first match. You can force an array with a boolean lookup, but FILTER is the purpose-built function for returning all matching rows.
Does FILTER spill its results?
Yes. FILTER writes all matching rows into adjacent cells (a dynamic array). The spill range must be empty, or you get a #SPILL! error. XLOOKUP returns a single value with no spill.
How do I filter by multiple conditions?
Multiply boolean conditions for AND: FILTER(C2:C8,(A2:A8="East")*(B2:B8="Widget")). Add them for OR: (A2:A8="East")+(A2:A8="West").
What happens when nothing matches?
XLOOKUP returns its if_not_found argument (4th). FILTER returns its if_empty argument (3rd); without it, FILTER errors (#CALC! in Excel, #N/A in this engine).
Can I sort FILTER's results?
Yes — wrap it: SORT(FILTER(B2:B8,A2:A8="East")). You can also deduplicate with UNIQUE. XLOOKUP returns one value and does not compose this way.
Which should I use to enrich a single record?
XLOOKUP. It returns a scalar that drops cleanly into another formula. FILTER returns an array that may spill, which is usually not what you want for a single-cell lookup.