Return Multiple Values for Each Unique Name in Excel
Question details
Extract unique names from one column and return all associated values horizontally or in a combined dynamic array.

- Product
- Microsoft Excel for Microsoft 365
- Device & OS
- not provided
- Scenario
- A worksheet contains repeated names and corresponding values in separate columns.
- Observed behavior
- Each unique name should appear once, with its matching values arranged across the same row.
Confirm that names are in A2:A100 and their corresponding values are in B2:B100, or adjust the ranges in the formulas. Use a version of Excel that supports the dynamic array functions in your chosen formula, and leave enough empty cells for the results to spill.
List Unique Names and Return Matching Values Across Each Row
Use UNIQUE and FILTER when you want an easy-to-read name list with related values beside each name.
In an empty cell such as D2, enter =UNIQUE(FILTER(A2:A100,A2:A100<>"")) to spill the nonblank unique names down the column.
In E2, enter =TRANSPOSE(FILTER($B$2:$B$100,$A$2:$A$100=D2)) to display the first name's matching values across the row.
Select E2 and drag its fill handle down alongside the names in column D. Leave the cells to the right empty so each result can spill.

Build One Combined Array of Names and Matching Values
Use a single formula when you want the names and their related values to spill together.
Work with Name and Value Lists in WPS Spreadsheets
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open Excel workbooks and continue working with your data; check advanced dynamic array formulas for compatibility before relying on them.

Frequently Asked Questions
Why does Excel show a #SPILL! error?
A dynamic array needs empty cells for its results. Clear any entries in the intended spill area, then recheck the formula.
How can I exclude blank names from the results?
Use =UNIQUE(FILTER(A2:A100,A2:A100<>"")) to filter out blank name cells before extracting unique names.
Why are there #N/A cells in my combined array?
VSTACK pads rows when names have different numbers of matching values. Wrap the result in IFNA, as shown in the combined formula, to display those errors as blanks.
Will these formulas work in older versions of Excel?
The formulas require dynamic array functions, and the combined formula also requires newer functions such as REDUCE and VSTACK. Check whether your Excel version supports each function before using it.




