Function Problems

Return Multiple Values for Each Unique Name in Excel

Adam DavisAdam Davis Nov 18, 2026 868 views

Question details

Extract unique names from one column and return all associated values horizontally or in a combined dynamic array.

Return Multiple Values for Each Unique Name in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Extract the unique names

In an empty cell such as D2, enter =UNIQUE(FILTER(A2:A100,A2:A100<>"")) to spill the nonblank unique names down the column.

2
Return values horizontally

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.

3
Fill the formula down

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.

List Unique Names and Return Matching Values Across Each Row
Update the filled formulas: The name list updates automatically when the source changes, but you must fill the formula in column E down to any newly added name rows.
Free Microsoft Office alternative

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.

Organize and review name and value lists in WPS SpreadsheetsOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar interface for an easier move from ExcelWork with a free, lightweight office suite
microsoft office alternative - wps office

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.