How to Return a Matching Unique Value for Duplicates in Excel
Question details
The user needs a formula to look up a unique nonblank value in one column that corresponds to a duplicate value in another column, specifically outputting the result only when the current target cell is blank.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Filling incomplete data sets or cleaning records where some duplicate entries have corresponding unique values missing in adjacent cells.
- Observed behavior
- The user wants to conditionally output the found nonblank value only if the current row's designated column is empty.
Ensure your data ranges are locked with absolute references (like $E$3:$E$12) so the formula maintains the correct target area when dragged down the column.
Use INDEX and MATCH Array Formula
This method uses IFERROR, IF, INDEX, and MATCH functions to precisely locate the nonblank corresponding value for a duplicate entry.
This formula first checks if the current cell in your target column is blank. If it is, it searches the array for a matching ID where the value column is not blank, returning that unique value while gracefully handling errors.
Click on the cell where you want the result to appear (e.g., cell G3).
Input the formula: =IFERROR(IF(F3="",INDEX($F$3:$F$12,MATCH(1,($E$3:$E$12=E3)*($F$3:$F$12<>""),0)),""),"")
If you are using an older version of Excel, you must press Ctrl+Shift+Enter to confirm it as an array formula. Newer versions with dynamic array support will calculate it automatically simply by pressing Enter.
Click and drag the fill handle at the bottom-right corner of the cell to apply the formula to the rest of your dataset.
Use an Alternative Nested INDEX Formula
If you prefer to avoid the Ctrl+Shift+Enter array constraint in older versions, an extra INDEX function can be nested inside MATCH to handle the array calculation natively.
Easily Manage Complex Array Formulas with WPS Spreadsheet
WPS Spreadsheet fully supports advanced array formulas, dynamic arrays, and nested INDEX/MATCH functions natively. It easily handles duplicate matching and data cleaning tasks with high performance and seamless compatibility.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your duplicate data.
- 2. Apply the lookup formula: Paste the INDEX/MATCH formula into the desired blank cell next to your data.
- 3. Fill the series: Double-click or drag the fill handle in the bottom-right corner of the cell to apply the formula across your dataset automatically.
Frequently Asked Questions
Why is my formula returning a #VALUE! error?
This typically happens in older spreadsheet versions if you press Enter instead of Ctrl+Shift+Enter when inputting an array formula. Make sure to confirm the formula properly so curly braces appear around it.
What does the '1' mean in the MATCH function in this formula?
The '1' acts as the lookup value representing TRUE. The criteria portion creates an array of 1s and 0s (TRUE and FALSE conditions), and MATCH searches for the first '1' where both the duplicate matches and the target cell is not blank.
How do I adjust the lookup ranges if my data expands?
To accommodate expanding data, update the absolute row numbers in your formula (e.g., change $12 to $1000). Alternatively, format your data range as a Table and use structured references, which automatically expand.
Can I use XLOOKUP instead of INDEX and MATCH for this?
Yes, if your version supports XLOOKUP, you can achieve similar results by using Boolean logic within the lookup array (e.g., XLOOKUP(1, (Range1=Value)*(Range2<>""), ReturnRange)). However, INDEX and MATCH provide broader compatibility with older versions.
