How to Find the Closest Match with Multiple Criteria in Excel
Question details
The user needs to retrieve a specific value, such as a commission structure, by finding a record that exactly matches a specific rate while having the closest numerical proximity to a target production multiplier.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Searching a dataset to return a value based on a dual-condition criteria where one condition requires an exact text/number match and the other requires finding the minimum absolute difference to a target number.
- Observed behavior
- Standard lookup functions like VLOOKUP or XLOOKUP typically find exact or approximate matches for a single criterion. Combining an exact match on one column with a closest match on another requires nested dynamic array formulas.
Ensure you are using a modern spreadsheet software that supports Dynamic Array functions such as LET, FILTER, and XLOOKUP. Verify that your lookup columns contain clean numeric data without accidental text formatting.
Use LET and FILTER combined with XLOOKUP
This method is highly readable and processes the data efficiently by filtering the exact match first, calculating the smallest difference, and retrieving the target result.
By utilizing the LET function, you can define variables within the formula to avoid repetitive calculations. We will first filter the data by the exact criteria, calculate the absolute difference to find the closest match, and then use XLOOKUP to return the final value.
Set aside specific cells for your search criteria. For example, place your target rate in cell J1 and your target multiplier in cell J2.
Select the cell where you want the result to appear and input the following formula: =LET(Dif,ABS(J2-FILTER($F$2:$F$12,$E$2:$E$12=J1)), MinDif,MIN(Dif), Com,FILTER($G$2:$G$12,$E$2:$E$12=J1), XLOOKUP(MinDif,Dif,Com))
Press Enter. The formula first filters column F by the exact rate in J1, subtracts it from your target multiplier in J2 to find the absolute difference, and then looks up the minimum difference to return the corresponding commission from column G.
Use XLOOKUP with Concatenated Arrays
A slightly different approach that evaluates the minimum difference and the exact match criteria simultaneously by joining them with a delimiter.
Use TAKE, SORT, and HSTACK for Array Manipulation
For users comfortable with advanced array shaping, you can sort the absolute differences and extract the top matching record directly.
Master Dynamic Arrays with WPS Spreadsheet
WPS Spreadsheet natively supports advanced dynamic array functions like XLOOKUP, FILTER, and LET, making it incredibly easy to solve complex multi-criteria lookup problems without performance lags.
- 1. Download and Install: Get WPS Office for free from the official website and install the suite on your Windows, Mac, or Linux device.
- 2. Open your Spreadsheet: Launch WPS Spreadsheet and open your existing Excel workbook containing your data.
- 3. Apply the Formula: Enter your LET, FILTER, or XLOOKUP formula directly into the cell. WPS Spreadsheet will calculate the arrays instantly.
Frequently Asked Questions
Why is my FILTER function returning an error?
The FILTER function might return a #CALC! or #VALUE! error if no records match your exact criteria (e.g., the specific rate is not found in the column). You can add a third argument to the FILTER function, such as "Not Found", to handle empty results gracefully.
Can I use INDEX and MATCH instead of XLOOKUP for multiple criteria?
Yes, you can combine INDEX and MATCH with array calculations to achieve a similar result. However, using XLOOKUP or LET combined with FILTER is much cleaner, requires less repetitive syntax, and is generally easier to troubleshoot.
Is the LET function required to find the closest match?
The LET function is not strictly required, but it significantly improves performance and formula readability. By defining variables, you avoid calculating the absolute difference multiple times, which keeps the formula efficient.
Will these formulas work in older versions of Excel or WPS?
Functions like LET, XLOOKUP, SORT, and FILTER are dynamic array functions available in modern spreadsheet tools like Microsoft 365, Excel 2021, and updated versions of WPS Office. If you are using an older version, you may need to rely on traditional Array Formulas entered with Ctrl+Shift+Enter.
