Excel guide · 4 min read

XLOOKUP vs FILTER: which one should you use?

Quick answerXLOOKUP returns one match: the first one it finds, or the last one if you ask. FILTER returns every row that matches, spilled down the sheet. Use XLOOKUP when each lookup should give one answer in one cell, like a price for a product code. Use FILTER when you want a list, like every order for a customer. Both need Excel for Microsoft 365, Excel 2021 or later, or Excel for the web.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

	A	B	C	D	E
1	Order	Customer	Product	Rep	Amount
2	SO-4101	Brightside Cafe	Espresso beans 5kg	Ana	240
3	SO-4102	Kestrel Hotels	Machine service	Ben	610
4	SO-4103	Brightside Cafe	Oat milk x24	Ana	86
5	SO-4104	Marlow Bakery	Espresso beans 5kg	Chloe	240
6	SO-4105	Kestrel Hotels	Paper cups x1000	Ben	132
7	SO-4106	Brightside Cafe	Grinder burrs	Dev	175
8	SO-4107	Pine & Co	Espresso beans 1kg	Chloe	58
9	SO-4108	Kestrel Hotels	Espresso beans 5kg x3	Ben	720

Download this sheet as an Excel practice file (.xlsx)

Same question, different answers

This order log has three orders from Brightside Cafe. Ask XLOOKUP for Brightside's amount and it stops at the first match, SO-4101, and returns 240. It doesn't warn you that there are two more.

That's exactly right when the lookup column holds unique values, such as order numbers, product codes or employee IDs. It's a silent mistake when the column has repeats, such as customer names, and you wanted all of them or the latest one.

=XLOOKUP("Brightside Cafe",B2:B9,E2:E9)

Result: 240

240: the amount on SO-4101, the first Brightside Cafe row. The other two Brightside orders are ignored.

FILTER returns every match

FILTER(array, include, [if_empty]) keeps every row where the include test is TRUE. Type it in one cell and the results spill down into the cells below, as many rows as there are matches. Add or remove a Brightside order and the list grows or shrinks by itself.

Because the result is a list, you can wrap it: =SUM(FILTER(E2:E9,B2:B9="Brightside Cafe")) adds the three amounts and returns 501, and ROWS(FILTER(...)) counts them. For a plain total, SUMIFS is shorter and works in every version, but FILTER lets you see the rows you're adding.

=FILTER(E2:E9,B2:B9="Brightside Cafe")

Result: spills 3 rows × 1 column

240

175

240, 86 and 175 in three cells: every Brightside Cafe amount, in the order they appear.

Want only the last match? XLOOKUP still wins

If the right answer is the most recent order, you don't need a list. XLOOKUP's sixth argument, search_mode, set to -1 searches from the bottom up and returns the last match. In a log where new rows go at the bottom, that's the latest one.

You could get the same with FILTER and TAKE, but one XLOOKUP is easier to read and returns a single value that fills down a column without spilling into its neighbours.

=XLOOKUP("Brightside Cafe",B2:B9,E2:E9,,0,-1)

Result: 175

175: the amount on SO-4106, the last Brightside Cafe order in the list.

When nothing matches: #N/A vs #CALC!

The two functions fail differently. XLOOKUP with no match returns #N/A; FILTER with no match returns #CALC!, meaning the result is empty. Both have a built-in argument for a friendlier message, so you don't need IFERROR: XLOOKUP's fourth argument is if_not_found, FILTER's third is if_empty.

=XLOOKUP("Zenith Gym",B2:B9,E2:E9,"No orders") shows No orders. The FILTER version below does the same. If you see #CALC! on a report that worked yesterday, the filter simply has nothing to return today.

=FILTER(E2:E9,B2:B9="Zenith Gym","No orders")

Result: No orders

No orders: there are no Zenith Gym rows, so FILTER shows the if_empty text instead of #CALC!.

Several columns and several conditions

Both can return more than one column. XLOOKUP returns one row across several columns when the return range is wider: =XLOOKUP("SO-4105",A2:A9,B2:E9) spills the customer, product, rep and amount for that order across four cells. FILTER returns whole rows for every match: =FILTER(A2:E9,B2:B9="Kestrel Hotels") gives a small table of all three Kestrel orders.

For several conditions, FILTER multiplies the tests, so both must be TRUE. XLOOKUP can do it too, by looking up 1 in the same multiplied tests: =XLOOKUP(1,(B2:B9="Kestrel Hotels")*(E2:E9>500),A2:A9) returns SO-4102, the first Kestrel order over 500. FILTER returns both, 610 and 720.

=FILTER(E2:E9,(B2:B9="Kestrel Hotels")*(E2:E9>500))

Result: spills 2 rows × 1 column

610

720

610 and 720: both Kestrel Hotels orders over 500. Use + instead of * for either condition.

Tables, spills and which to pick

FILTER always spills, so it needs empty cells below (and to the right, for several columns). Anything in the way gives #SPILL!. Spilling formulas also can't live inside an Excel table, so put a FILTER report next to the table, or on its own sheet, and point it at the table's columns. A single-value XLOOKUP is fine in a table column and fills down like any formula.

Pick XLOOKUP for one answer per row: prices, names, rates, the latest status, a value next to an ID. Pick FILTER for a list or a mini report: every order for a customer, every overdue task, every row for a region, especially when someone picks the criteria from a drop-down. If you find yourself writing XLOOKUP and hoping there's only one match, check with COUNTIF first, or use FILTER.

Common mistakes

Using XLOOKUP on a column with repeats and expecting all matches

XLOOKUP returns the first match only. Use FILTER for every match, or XLOOKUP with search_mode -1 for the last.

Putting a FILTER formula inside an Excel table

Spilling formulas don't work in tables and show #SPILL!. Put the FILTER next to the table or on another sheet.

Wrapping both in IFERROR to hide no-match errors

Use XLOOKUP's if_not_found or FILTER's if_empty. IFERROR also hides real mistakes, like a typo in a range.

Filling a FILTER formula down a column like a lookup

Type FILTER once at the top of the output. Copies below sit in its spill range, and it shows #SPILL!.

FAQ

Is FILTER better than XLOOKUP?

Neither is better; they answer different questions. XLOOKUP gives one value per lookup, FILTER gives every matching row. Use FILTER when the answer is a list.

Can XLOOKUP return multiple matches?

No. XLOOKUP returns the first match, or the last with search_mode -1. It can return several columns for that one match, but for several matching rows you need FILTER.

Why does FILTER return #CALC!?

Nothing matched the include test, so the result is empty. Add a third argument, such as "None found", to show a message instead.

Do XLOOKUP and FILTER work in Excel 2019?

No. Both need Excel for Microsoft 365, Excel 2021 or later, or Excel for the web. In Excel 2019 they show #NAME?. Use INDEX and MATCH there instead.

Which is faster, XLOOKUP or FILTER?

For one value, a single XLOOKUP is light. For a list, one FILTER is usually faster and simpler than many XLOOKUPs or helper columns. On normal-sized sheets you won't notice a difference.

Functions used

Related guides

Updated November 7, 2026