How to Use INDEX and MATCH for Duplicate Values in Excel
Question details
The user needs to retrieve multiple different records associated with a duplicated lookup value instead of getting the same result repeatedly.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Looking up data in a spreadsheet where the search key (like a code or ID) appears multiple times in the source dataset.
- Observed behavior
- Standard INDEX and MATCH or VLOOKUP formulas repeatedly return only the first matching record, failing to retrieve subsequent unique entries for the duplicated code.
Ensure your data ranges are accurate and structured properly. Consider converting your dataset into a Table, which makes handling complex formulas easier to read and manage.
Use an Array Formula with SMALL, IF, ROW, and COUNTIF
Use a complex array formula to dynamically count occurrences and return the 1st, 2nd, and 3rd match as you drag the formula down the column.
Standard lookup formulas stop at the first match. To bypass this, you can combine INDEX with SMALL, IF, ROW, and COUNTIF to force the formula to look for the next occurrence corresponding to the row you are currently filling.
Click on the cell where you want to output the first instance of your duplicated value.
Type the formula: =IFERROR(INDEX($B$4:$E$13,SMALL(IF($B$4:$B$13=$G4,ROW($B$4:$B$13)-ROW($B$3)),COUNTIF($G$4:G4,$G4)),MATCH(H$3,$B$3:$E$3,0)),""). Ensure you adjust the cell references to match your actual data layout.
Instead of just pressing Enter, press Ctrl + Shift + Enter to evaluate it as an array formula. Curly braces {} will appear around your formula in the formula bar.
Use the fill handle at the bottom right corner of the cell to drag the formula down. The COUNTIF function will increment automatically, pulling the 2nd, 3rd, and subsequent matches.
Use a Helper Column for Unique Lookup Values
Create a unique identifier by combining two columns, which allows standard INDEX and MATCH formulas to function without complex array formulas.
Handle Complex Lookups Seamlessly with WPS Spreadsheet
WPS Office provides full support for advanced array formulas, helper columns, and dynamic functions. You can easily manage duplicates and process complex datasets with an interface identical to Microsoft Excel.
- 1. Open your dataset: Launch WPS Spreadsheet and open the document containing the duplicate values you want to process.
- 2. Input the array formula: Select the target cell and enter your INDEX, SMALL, IF, and MATCH combination.
- 3. Execute the array: Press Ctrl + Shift + Enter to correctly process the array calculation and retrieve your exact duplicate match.
- 4. Apply to multiple rows: Drag the fill handle to effortlessly populate the rest of your ticket numbers or costs.
Frequently Asked Questions
Why does VLOOKUP or INDEX and MATCH only return the first value?
By design, standard lookup functions in spreadsheet software search from the top down and immediately stop searching as soon as they find the first exact match for your criteria.
Do I always need to press Ctrl+Shift+Enter for these formulas?
If you are using the complex SMALL(IF()) array combination in traditional versions of Excel or WPS Spreadsheet, you must use Ctrl+Shift+Enter. Modern dynamic array updates handle this natively, but the shortcut ensures backward compatibility.
Is there an easier function to return multiple matching records?
Yes. If you have a newer version of Excel or WPS Office that supports dynamic arrays, you can use the FILTER function to easily extract all matching records based on your criteria without using complex INDEX and MATCH combinations.
