Cellwise

Excel guide · 2 min read

How to return multiple matches in Excel

Use =FILTER(B2:B9,A2:A9="Sales"). FILTER returns every value from column B where column A equals Sales, spilling the results down. VLOOKUP and XLOOKUP only return the first match. FILTER needs Excel 365 or Excel 2021 and later.

Sample data

Every formula on this page was calculated live against this sheet.

ABCD
1DeptEmployeeStart yearRating
2SalesNina Ross20194
3ITOmar Haddad20215
4SalesBen Carter20233
5HRJulia Kim20184
6SalesZoe Martin20205
7ITRaj Singh20244
8SalesEli Brooks20224
9HRMaya Lopez20213

Return every match with FILTER

FILTER takes the values to return and a condition. It returns all rows where the condition is TRUE and spills them into the cells below, so leave those cells empty.

The condition is a comparison on a whole column, like A2:A9="Sales". In a real sheet, point it at an input cell instead, such as A2:A9=G1, so you can switch departments by typing in G1.

fx=FILTER(B2:B9,A2:A9="Sales")

Result: Nina Ross spills 4×1

Nina Ross
Ben Carter
Zoe Martin
Eli Brooks

Spills four names: Nina Ross, Ben Carter, Zoe Martin and Eli Brooks.

Return whole rows, not one column

Give FILTER a wider range to return several columns at once. Here we get the name, start year and rating for each IT employee.

fx=FILTER(B2:D9,A2:A9="IT")

Result: Omar Haddad spills 2×3

Omar Haddad20215
Raj Singh20244

Spills two rows of three columns, for Omar Haddad and Raj Singh.

Several conditions

Multiply conditions for AND: both must be true. Use + for OR. Put each condition in its own brackets.

fx=FILTER(B2:B9,(A2:A9="Sales")*(D2:D9>=4))

Result: Nina Ross spills 3×1

Nina Ross
Zoe Martin
Eli Brooks

Returns Nina Ross, Zoe Martin and Eli Brooks: Sales staff rated 4 or higher.

Show a message when nothing matches

With no matches, FILTER returns #CALC!. The third argument sets what to show instead.

fx=FILTER(B2:B9,A2:A9="Legal","No one found")

Result: No one found

Returns "No one found".

Sort the results or put them in one cell

Wrap FILTER in SORT to order the list: =SORT(FILTER(B2:B9,A2:A9="Sales")).

To list every match in a single cell, for a summary table or a label, wrap it in TEXTJOIN with a separator.

fx=TEXTJOIN(", ",TRUE,FILTER(B2:B9,A2:A9="HR"))

Result: Julia Kim, Maya Lopez

Returns "Julia Kim, Maya Lopez" in one cell.

In Excel 2019 and earlier

FILTER is not available there. The classic workaround combines INDEX, SMALL, IF and ROW in a formula entered with Ctrl+Shift+Enter, copied down until it returns errors. It works but is hard to maintain.

A simpler option is Data > Filter: click the arrow on the Dept column, tick only Sales, and copy the visible rows. Advanced Filter (Data > Sort & Filter > Advanced) can copy matching rows to another location.

Common mistakes

#SPILL! error

Something is in the way of the results. Clear the cells below and to the right of the formula.

#CALC! when nothing matches

Add the if_empty argument, like "None", as the third argument.

Ranges of different heights

The return range and every condition range must cover the same rows, or FILTER returns #VALUE!.

Using AND() or OR() for the conditions

They return a single TRUE or FALSE for the whole range. Use * for AND and + for OR.

FAQ

Can VLOOKUP return multiple values?

No. VLOOKUP and XLOOKUP stop at the first match. Use FILTER to return all matches.

How do I return multiple matches in one cell?

Wrap FILTER in TEXTJOIN: =TEXTJOIN(", ",TRUE,FILTER(B2:B9,A2:A9="Sales")). This needs Excel 365 or 2021.

Can FILTER match partial text?

Yes. Combine it with SEARCH: =FILTER(B2:B9,ISNUMBER(SEARCH("ma",B2:B9))) returns every name that contains "ma", ignoring case.

Does FILTER update automatically?

Yes. Change the source data or the condition and the results update straight away. Turn the data into a table (Ctrl+T) so new rows are included too.

Functions used

Keep learning

Updated 2026-10-31