'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Return Different Names for Duplicate Values in Excel
Question details
The user needs a method to retrieve multiple distinct names from one column that correspond to duplicate numeric values in another column.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Pulling multiple records or names (like player ratings) that share the exact same numeric value in the lookup column.
- Observed behavior
- Standard lookup functions like VLOOKUP only return the first matching instance, failing to extract all consecutive matches for the duplicate value.
Ensure you clear any existing formulas or data in your target output columns before entering the new array formula to prevent calculation conflicts.
Use an INDEX and SMALL Array Formula
Combine IFERROR, INDEX, SMALL, IF, and ROW functions to iterate through a data range and extract all matching names sequentially.
In older versions of Excel such as Excel 2007, you cannot rely on dynamic array functions like FILTER. Instead, you must use a traditional array formula.
This formula works by generating an array of row numbers where the condition is met, and then using the SMALL function to extract them one by one as you copy the formula down.
Highlight the cells in your target columns (for example, AD and AE) and press Delete to clear out any old formulas.
Select the first cell where you want the name to appear (e.g., AD2) and input the formula: =IFERROR(INDEX('player ratings'!$B$5:$B$756,SMALL(IF('player ratings'!$J$5:$J$756=AE26,ROW('player ratings'!$B$5:$B$756)-ROW('player ratings'!$B$5)+1),ROWS($AE$26:$AE26))),"")
Do not simply press Enter. Instead, hold down Ctrl + Shift and then press Enter. This tells the application to evaluate it as an array formula, surrounding it with curly braces {}.
Click and drag the fill handle at the bottom-right corner of the cell downwards to list all the multiple names. The IFERROR function will ensure that blank cells are displayed once all names for that numeric value are found.
Easily Handle Complex Data Lookups with WPS Spreadsheet
WPS Spreadsheet fully supports advanced array formulas, including INDEX and SMALL combinations, allowing you to seamlessly extract duplicate values just as you would in Microsoft Excel.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your data.
- 2. Input the array formula: Paste the INDEX/SMALL formula into your target cell.
- 3. Evaluate the formula: Press Ctrl + Shift + Enter to properly execute the array calculation.
- 4. Fill the series: Drag the fill handle down to extract all corresponding names for the duplicate values.
Frequently Asked Questions
Why does standard VLOOKUP not work for duplicate values?
VLOOKUP is designed to scan down a column and return only the first matching record it encounters. To extract multiple different names for the same lookup value, you must use an array formula like INDEX and SMALL, or the newer FILTER function if your software supports it.
What does pressing Ctrl+Shift+Enter do?
Pressing Ctrl+Shift+Enter evaluates the formula as an array formula. This allows functions like IF and ROW to process an entire range of values (an array) at once rather than a single cell, which is necessary for the INDEX and SMALL combination to work.
Why is my formula returning a blank cell?
The formula uses the IFERROR function to return a blank ("") when there are no more matching names to list. If it returns blank for the very first cell, double-check your cell references, ensure the lookup value exists in the source column, and verify that you confirmed the formula with Ctrl+Shift+Enter.
Can I use the FILTER function instead of this complex formula?
Yes, if you are using newer versions of Excel (Excel 365/2021) or updated versions of WPS Office that support dynamic array functions, you can use the much simpler FILTER function, which automatically spills the results and does not require Ctrl+Shift+Enter.
