Return an Unused Unique Match Based on Multiple Criteria in Excel
Question details
The user needs an Excel formula to find an ID that matches three specific criteria but has not been used or matched previously, returning N/A if none are available.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Assigning or matching unique IDs based on multiple conditions without reusing already matched IDs.
- Observed behavior
- The formula needs to identify the first available unique ID meeting all criteria, or return an N/A error if all matching IDs have already been used.
Ensure your spreadsheet software supports Dynamic Array formulas such as FILTER and UNIQUE. Prepare a separate column or range that tracks which IDs have already been matched.
Use Dynamic Array Formulas to Find an Unused Match
Combine FILTER, COUNTIF, and IFERROR within a LET function to cleanly extract the first unused ID matching your criteria.
This approach uses the FILTER function to narrow down the dataset based on your three criteria. It then checks against a list of already used IDs using COUNTIF to ensure no duplicates are assigned.
The LET function makes the formula easier to read and compute by assigning names to intermediate calculation results, while UNIQUE ensures you are looking at distinct IDs.
Set up your source data with the ID column and the three criteria columns. Ensure you also have a designated column that lists the IDs that have already been matched.
Use the FILTER function to include rows where Criteria1, Criteria2, and Criteria3 match your target values. The syntax looks like: (Range1=Target1)*(Range2=Target2)*(Range3=Target3).
Add a condition to the FILTER function using COUNTIF(Used_IDs_Range, ID_Range)=0 to ensure only IDs that haven't been matched yet are considered in the array.
Wrap the filtered results in the UNIQUE function to remove duplicates, and use the INDEX function, such as INDEX(..., 1), to return just the first available unused ID.
Enclose the entire formula in an IFERROR(..., NA()) function so that it returns #N/A if no unused unique IDs are found.
Use Advanced Dynamic Arrays Easily with WPS Spreadsheet
WPS Spreadsheet fully supports modern dynamic array functions like FILTER, UNIQUE, and LET, making it easy to build complex multi-criteria matching formulas without slowing down your computer.
- 1. Open your spreadsheet: Launch WPS Office and open your .xlsx file containing the ID dataset and criteria.
- 2. Enter the formula: Select the target cell and input your combined LET, FILTER, and COUNTIF formula to find the unused match.
- 3. Calculate and drag: Press Enter to execute the dynamic array formula. Drag the fill handle down if you need to generate multiple unique matches for different rows.
Frequently Asked Questions
Why does my FILTER formula return a #CALC! error?
The #CALC! error typically occurs when the FILTER function finds no results matching your criteria. In this scenario, you can wrap your formula in IFERROR or use the [if_empty] argument in the FILTER function to return NA() instead.
Can I do this without the LET function?
Yes, you can write the formula without LET by repeating the FILTER and COUNTIF expressions where needed. However, using LET makes the formula shorter, easier to read, and faster to calculate by storing intermediate results.
How do I add a fourth criterion to the match?
To add another criterion, simply append another multiplication condition inside the FILTER function's include argument. For example, add *(Range4=Target4) to your existing criteria string.
Will this formula work in older versions of Excel?
No, functions like FILTER and UNIQUE are dynamic array formulas introduced in recent versions. For older versions, you would need complex array formulas using INDEX, MATCH, and COUNTIFS, which must be entered with Ctrl+Shift+Enter.
