How to Return All Matching Values for a Repeated Name in Excel
Question details
The user needs to return every matching value from a lookup column when a customer or manufacturer name appears more than once, using a spill range or array formula.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user has a list or table where a manufacturer or customer name repeats across rows and wants to return all related part numbers or values into a spill range.
- Observed behavior
- The goal is to have every matching value appear automatically from one formula instead of retrieving only the first match.
Make sure your data is arranged in a clear table or consistent columns, and confirm whether your Excel version supports the FILTER function, which is available in Microsoft 365 and Excel 2021 or later.
Use the FILTER Function to Return All Matching Values
Best for Microsoft 365, Excel 2021, and WPS versions that support dynamic array functions, because it spills every matching value automatically.
FILTER is the simplest approach when your data is stored in an Excel table. The formula returns a dynamic spill range, so you do not need to copy it down multiple rows.
Click any cell inside the manufacturer and part number range, press Ctrl+T, enable My table has headers, and rename the table to PartNums on the Table Design tab.
Select the first result cell, type =FILTER(PartNums[PN],PartNums[Manufacturer]=E$1,"") and press Enter. The matching values appear in the spill range below the formula.
If your table or columns use different names, replace PartNums, PN, Manufacturer, and E$1 with your actual table name, return column, lookup column, and criteria cell.
Use an INDEX, SMALL, and IF Array Formula in Older Excel Versions
Use this when FILTER is not available in your Excel version, or when you need a non-spilling formula that can be copied across a fixed range.
Return Every Matching Value in WPS Spreadsheets with the FILTER Formula
Open the same workbook in WPS Spreadsheets and use the FILTER formula or legacy array formula to return all matching values for a repeated name, just as you would in Excel.
- 1. Open the workbook in WPS Spreadsheets: Click the WPS Office home screen or choose File > Open, select the Excel workbook, and confirm the data range or table is available.
- 2. Use the FILTER formula: Click the first result cell, type =FILTER(PartNums[PN],PartNums[Manufacturer]=E$1,"") and press Enter.
- 3. Confirm the spill range: Check that all matching values appear below the formula cell; WPS displays the results in the same spill layout as Excel.
- 4. Save in Excel-compatible format: Press Ctrl+S or choose Menu > Save As, then select .xlsx to keep the workbook compatible with Excel.
Frequently Asked Questions
Can I use the FILTER function in WPS Spreadsheets?
Yes. Current WPS Spreadsheets versions that support dynamic arrays can use FILTER. Open the workbook, click a result cell, enter the formula, and press Enter. If FILTER is not available, use the INDEX, SMALL, and IF array formula instead.
What should I do if my Excel version does not have FILTER?
Use the INDEX, SMALL, and IF array formula and press Ctrl+Shift+Enter. It returns matching values in older Excel versions without dynamic arrays.
How do I make the formula update automatically when new rows are added?
Convert the data to an Excel table with Ctrl+T and refer to table columns such as PartNums[PN] and PartNums[Manufacturer]. Table references expand automatically as new rows are added.
Why does my formula return 0 or blank cells?
Wrap the formula with IFERROR or use the third FILTER argument, "", to display an empty string when there are no more matching values instead of showing an error or 0.
Can I return matches based on more than one condition?
Yes. With FILTER, multiply or add conditions inside the include argument, for example =FILTER(PartNums[PN],(PartNums[Manufacturer]=E$1)*(PartNums[Region]=F$1),"").
