XLOOKUP vs FILTER: One Match or All Matches?

The choice in XLOOKUP vs FILTER comes down to one question: do you want one match or all of them? XLOOKUP returns a single match, the first by default, so it suits "what is the price of this code?". FILTER returns every matching row and spills them into the cells below, so it suits "show me all orders for this customer". This comparison is for anyone on Microsoft 365 or Excel 2021 who uses one and wonders when the other is better.

XLOOKUP vs FILTER at a glance#

XLOOKUP is a lookup: one key in, one answer out. FILTER is a query: one test in, every matching row out.

How each function works#

XLOOKUP searches one column for a value and returns the matching item from another column. It stops at the first match. The XLOOKUP function guide and XLOOKUP for beginners cover each argument.

FILTER checks a test on every row and keeps the rows where it is TRUE. The include argument is a test such as B2:B7="Acme Ltd", which gives TRUE or FALSE for each row. The FILTER function guide explains the AND and OR versions.

The same data, side by side#

Here is a small order list. Acme Ltd has three orders, which is where the two functions differ.

G1 holds Acme Ltd.

Task 1: the amount for Acme Ltd#

=XLOOKUP(G1,B2:B7,D2:D7)
=FILTER(D2:D7,B2:B7=G1)

XLOOKUP returns 120, the first Acme order, and says nothing about the other two. FILTER spills 120, 890 and 210 into three cells. Neither is wrong; they answer different questions. If the customer column should have only one row per customer, XLOOKUP is right. If not, FILTER shows you what XLOOKUP hides.

Task 2: the most recent order#

If rows are in date order, the last match is the latest. XLOOKUP can search from the bottom with search mode -1:

=XLOOKUP(G1,B2:B7,D2:D7,,0,-1)

The result is 210 (order 1005). Find the last match with XLOOKUP explains the arguments.

Task 3: every column of every Acme order#

=FILTER(A2:D7,B2:B7=G1)

This spills a 3-row, 4-column block: orders 1001, 1003 and 1005 with their customer, product and amount. XLOOKUP can return the four columns, =XLOOKUP(G1,B2:B7,A2:D7), but only for order 1001. See return every match for a lookup.

Task 4: two conditions (Acme's toner order)#

FILTER multiplies tests, where * means AND:

=FILTER(D2:D7,(B2:B7=G1)*(C2:C7="Toner"))
=XLOOKUP(1,(B2:B7=G1)*(C2:C7="Toner"),D2:D7)

Both return 210. The XLOOKUP version looks for the first row where the multiplied tests equal 1. It works, but it is harder to read. Lookup with multiple criteria covers both.

Task 5: a customer with no orders#

=XLOOKUP("Dunmore",B2:B7,D2:D7,"No orders")
=FILTER(D2:D7,B2:B7="Dunmore","No orders")

Both show No orders. Without the last argument, XLOOKUP shows #N/A and FILTER shows #CALC!.

When to use XLOOKUP#

Use XLOOKUP when each lookup value should give one answer. It is the better choice when:

• You look up by a unique key, such as an employee ID, SKU or invoice number.
• You fill a column of results, one per row, such as pulling a price into every line of an order. FILTER's spills would collide.
• You need approximate or wildcard matching, such as tax bands or "name contains". See XLOOKUP wildcards and match modes.
• You need to look left. XLOOKUP's return column can be anywhere; see how to look up to the left.

When to use FILTER#

Use FILTER when a question can have many answers. It is the better choice when:

• You want all matching rows, such as every open ticket or every order for a customer.
• You're building a live report that grows and shrinks as data changes.
• You have several AND or OR conditions. Multiply tests for AND, add them for OR.
• You want to feed the matches into another function, such as =SORT(FILTER(...),4,-1) for a sorted list, or =ROWS(FILTER(...)) for a count.

For a total of all matches, though, =SUMIFS(D2:D7,B2:B7,G1) is simpler than =SUM(FILTER(D2:D7,B2:B7=G1)). Both return 1,220.

What to use in Excel 2019 or older#

XLOOKUP and FILTER arrived together with dynamic arrays, so a file that uses either shows #NAME? in Excel 2019, 2016 and earlier. Each has an older stand-in:

The VLOOKUP version returns 120, just like XLOOKUP, because it also stops at the first match. AutoFilter shows all matching rows, but it hides rows in place rather than copying them to a new spot, and it doesn't update by itself. FILTER function vs AutoFilter and VLOOKUP vs INDEX MATCH cover these older tools.

Common mistakes with XLOOKUP and FILTER#

1. Using XLOOKUP when keys repeat. It quietly returns the first match. Check with =COUNTIF(B2:B7,G1); if it is more than 1, you probably want FILTER.
2. Blocking FILTER's spill. Anything in the cells below or beside the formula causes #SPILL!. Clear the space; see the #SPILL! error.
3. Using AND() or OR() inside FILTER. They return one value for the whole range. Multiply or add tests in parentheses instead.
4. Ranges of different heights. lookup_array and return_array in XLOOKUP, and array and include in FILTER, must cover the same rows.
5. Including headers. FILTER(A1:D7,B1:B7=G1) works but risks matching the header row. Start ranges at row 2.
6. Sending the file to Excel 2019. Both show #NAME? there. Use VLOOKUP or INDEX MATCH for one match, and AutoFilter for many.

Our recommendation#

Use XLOOKUP as your default for single answers keyed on an ID, and FILTER whenever "all of them" is the honest answer. A good habit: before you trust an XLOOKUP on names or customers, count the matches. If there can be more than one, FILTER is the safer function, because it shows everything instead of hiding all but the first.

Both are core skills for analyst roles; see the Excel skills every data analyst needs. If you also choose between label functions, our comparison of CHOOSE vs SWITCH uses the same "which fits the question" approach, and LEFT vs TEXTBEFORE does the same for splitting text.

In short: XLOOKUP for one match, FILTER for all matches.

Practise it#

List Acme's products with FILTER in the exercise on this page. Then practise single lookups in the XLOOKUP lesson, and AND, OR and "no results" cases in the FILTER function lesson.