Extract Unique Values in Excel Using INDEX, MATCH, and COUNTIF
Question details
The user wants to extract a list of unique items from a dataset using an INDEX, MATCH, and COUNTIF array formula and successfully copy the formula across columns without breaking the references.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Extracting unique values from a dataset and applying the array formula to multiple output columns while maintaining the correct criteria ranges.
- Observed behavior
- Formula references change unexpectedly when copied to other columns due to incorrect relative ranges or table reference behavior, breaking the unique extraction logic.
Ensure your source data does not contain completely empty rows within the range, as this can return a zero instead of a blank. If you are using a modern spreadsheet version, check if the newer UNIQUE function is available, as it is significantly simpler to use.
Construct the INDEX, MATCH, and COUNTIF Array Formula
Use a combination of INDEX to return the value, MATCH to find the first instance not yet extracted, and COUNTIF with an expanding range to track previous results.
This advanced array formula identifies which values have already been extracted to the destination column and searches the source data for the next unextracted item.
Select the first cell in your destination column where you want the unique list to begin.
Write the COUNTIF part of the formula using an expanding range to check the cells directly above the current cell (e.g., COUNTIF($C$1:C1, source_range)). This tests the source range against values already returned.
Nest the COUNTIF function inside MATCH to find the position of the first '0' result: MATCH(0, COUNTIF($C$1:C1, source_range), 0).
Wrap the entire logic in the INDEX function to return the actual value: =INDEX(source_range, MATCH(0, COUNTIF($C$1:C1, source_range), 0)). Press Ctrl+Shift+Enter if your version requires array formulas to be entered this way, then drag the formula down.
Adjusting the Formula for Multiple Columns
Ensure formula stability when copying the unique value extraction logic to adjacent columns by fixing relative and absolute references.
Easily Extract Unique Values with WPS Office
WPS Spreadsheet fully supports advanced array formulas, including INDEX, MATCH, and COUNTIF, allowing you to manipulate complex datasets seamlessly. It also provides the modern UNIQUE function to achieve this result in a single step.
- 1. Open your data in WPS Spreadsheet: Launch WPS Office and open the workbook containing the data you want to filter.
- 2. Select a destination cell: Click an empty cell where you want the distinct values to populate.
- 3. Use the UNIQUE function: Type =UNIQUE(A2:A100) (replacing with your actual source range) and press Enter. The distinct list will spill automatically.
- 4. Alternatively, use the classic formula: If you prefer, paste the traditional INDEX, MATCH, and COUNTIF formula, press Enter, and drag the fill handle down to extract the list.
Frequently Asked Questions
Why does my INDEX/MATCH/COUNTIF formula return a #N/A error?
This typically occurs when the formula runs out of unique values to extract from the source list. You can hide this error by wrapping your entire formula in the IFERROR function, like =IFERROR(INDEX(...), ""), which returns a blank cell instead.
Do I need to press Ctrl+Shift+Enter for this formula?
In older spreadsheet software versions before Dynamic Arrays were introduced, you must press Ctrl+Shift+Enter to evaluate the INDEX, MATCH, and COUNTIF combination properly as an array formula. Modern versions process array formulas automatically with just the Enter key.
Is there an easier way to get unique values instead of combining three functions?
Yes. If you are using the latest versions of Microsoft Excel or WPS Office, you can use the dedicated UNIQUE function. Simply typing =UNIQUE(A2:A50) will instantly generate an array of distinct values without requiring complicated logic.
