Return Every Match for a Lookup in Excel (FILTER)

Lookup functions such as VLOOKUP and XLOOKUP return the first match only. When a lookup needs to return multiple values, such as every order for one customer, use FILTER: =FILTER(B2:B7,A2:A7=F2). This guide shows how to list every match, return whole rows, join the matches into one cell, sort and count them, and what to do if you are on an older version of Excel.

Why VLOOKUP and XLOOKUP only return one value#

Every classic lookup searches from the top and stops at the first match. Here is an order list where Brightline appears three times:

With Brightline in F2, =XLOOKUP(F2,A2:A7,B2:B7) returns SO-1001 and nothing else. That's correct for a one-to-one lookup, such as an ID to a name, but a customer can have many orders. The XLOOKUP guide covers single-match lookups in detail.

How to return multiple values with FILTER#

FILTER returns every value whose row passes a test.

=FILTER(array, include, [if_empty])

In G2, type:

=FILTER(B2:B7,A2:A7=F2)

• B2:B7 is what to bring back: the order numbers.
• A2:A7=F2 is the test, checked on each row. Rows where the customer equals F2 are kept.

The result spills into G2:G4: SO-1001, SO-1003 and SO-1005. You type the formula once, in G2 only, and Excel fills the cells below. Change F2 to Northwind and the list shrinks to two orders.

FILTER needs Excel 2021, Microsoft 365 or Excel for the web. See the FILTER function guide for the full syntax.

Keep the spill area empty#

If any cell in G3:G4 already holds something, FILTER shows #SPILL! instead of the list. Clear the cells below the formula, or move it. The #SPILL! error guide lists every cause.

Handle "no match" with if_empty#

When nothing matches, FILTER returns #CALC!. Add a third argument to show your own message:

=FILTER(B2:B7,A2:A7=F2,"No orders")

For a customer with no orders, the result is the text No orders.

How to return whole rows or several columns#

Make the first argument wider and FILTER returns several columns for every match:

=FILTER(B2:D7,A2:A7=F2,"No orders")

This spills a 3-row, 3-column block: order, product and amount for each Brightline order. Leave enough empty cells to the right as well as below.

The columns don't have to be next to the lookup column, and they can be to its left. FILTER doesn't care about column order, which makes it another answer to looking up to the left.

How to put every match in one cell with TEXTJOIN#

To show all the matches in a single cell, wrap FILTER in TEXTJOIN:

=TEXTJOIN(", ",TRUE,FILTER(B2:B7,A2:A7=F2))

The result is one text value: SO-1001, SO-1003, SO-1005. The first argument is the separator, and TRUE tells TEXTJOIN to skip empty cells. This is handy for summary tables with one row per customer, where a spilling list would collide with the next row.

How to sort, count and add up the matches#

FILTER results can go straight into other functions:

For counting and totals alone, COUNTIF and SUMIF are simpler and work in every version. Use FILTER when you also need to see the matching rows.

Return matches for two or more conditions#

Multiply tests for AND, and add them for OR, each in its own brackets.

• AND: Brightline orders of 100 or more: =FILTER(B2:B7,(A2:A7=F2)*(D2:D7>=100)) returns SO-1001 and SO-1005.
• OR: orders from Kestrel or Northwind: =FILTER(B2:B7,(A2:A7="Kestrel")+(A2:A7="Northwind")) returns SO-1002, SO-1004 and SO-1006.

Don't use AND() or OR() functions inside FILTER: they collapse the whole range into one TRUE or FALSE. If you only need one row that meets several conditions, see lookup with multiple criteria.

How to return multiple matches in Excel 2019 and earlier#

Without FILTER, you build the list one row at a time: each cell returns the 1st, 2nd, 3rd match. In G2, enter this and fill it down a few rows:

=IFERROR(INDEX($B$2:$B$7,AGGREGATE(15,6,(ROW($A$2:$A$7)-ROW($A$2)+1)/($A$2:$A$7=$F$2),ROWS($G$2:G2))),"")

How it works:

1. (ROW($A$2:$A$7)-ROW($A$2)+1) numbers the rows 1 to 6.
2. Dividing by ($A$2:$A$7=$F$2) keeps the row numbers that match and turns the rest into #DIV/0! errors.
3. AGGREGATE(15,6,...,k) returns the k-th smallest number while ignoring errors (15 is SMALL, 6 means ignore errors).
4. ROWS($G$2:G2) is 1 in G2, 2 in G3 and so on, so each row asks for the next match.
5. INDEX returns the order number, and IFERROR shows a blank once the matches run out.

It works in Excel 2010 and later without Ctrl+Shift+Enter, but it is much harder to read than FILTER. If your team is on Microsoft 365, use FILTER.

FILTER vs XLOOKUP vs a filter on the sheet#

The FILTER function updates as soon as the data or F2 changes, which makes it ideal for small reports and dashboards. If you only need the most recent match rather than all of them, see how to find the last match with XLOOKUP. To get a match's position rather than its value, use the MATCH function. Returning every match is a common step in analyst tasks; the Excel skills for data analysts guide shows where it fits.

Practise it#

Try the live exercise on this page to list every Brightline order. Then practise FILTER in a live spreadsheet, including AND, OR and if_empty, and use the XLOOKUP lesson to see exactly where single-match lookups stop.