How to Use XLOOKUP to Find the Closest Match in Excel?
Finding the closest match in Excel is a common task when working with prices, tax slabs, commission ranges, discounts, or grading systems. With the introduction of XLOOKUP, Microsoft Excel made this process much simpler and more flexible than older functions like VLOOKUP or HLOOKUP.
In this guide, you will learn how to use XLOOKUP to find the closest match, including next smaller and next larger values. Every step is explained using simple language and real examples so beginners can follow along easily.
Understanding Closest Match in Excel
A closest match means finding a value that is nearest to your lookup value rather than an exact match.
There are two common closest match scenarios:
- Next smaller value (less than or equal to the lookup value)
- Next larger value (greater than or equal to the lookup value)
These are widely used in pricing tables, commission brackets, tax rates, and grading systems.
XLOOKUP Syntax for Closest Match
The basic syntax of XLOOKUP is:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])The key argument for closest match is match_mode.
Match Mode Options
| Match Mode | Meaning |
|---|---|
| 0 | Exact match (default) |
| -1 | Exact match or next smaller |
| 1 | Exact match or next larger |
| 2 | Wildcard match |
For closest match, we mainly use -1 and 1.
How to Find the Closest Smaller Match Using XLOOKUP
This method returns the largest value that is less than or equal to the lookup value.
Example Data
| Quantity | Price |
|---|---|
| 1 | 100 |
| 10 | 90 |
| 50 | 80 |
| 100 | 70 |
If a customer buys 35 units, the closest smaller quantity is 10, so the price should be 90.
Formula
=XLOOKUP(E2, A2:A5, B2:B5, , -1)Explanation
- E2 contains the lookup value (35)
- A2:A5 is the quantity range
- B2:B5 is the price range
- -1 tells Excel to return the next smaller match
This approach works even if the lookup value does not exist in the table.
How to Find the Closest Larger Match Using XLOOKUP
This method returns the smallest value that is greater than or equal to the lookup value.
Using the same table, if a rule requires rounding up, the closest larger quantity for 35 units is 50, returning a price of 80.
Formula
=XLOOKUP(E2, A2:A5, B2:B5, , 1)Explanation
- 1 forces XLOOKUP to find the next larger match
- Useful for minimum charges, shipping slabs, or penalty rules
Using XLOOKUP with Sorted and Unsorted Data
Unlike older lookup functions, XLOOKUP does not require sorted data for exact matches.
However, when using closest match, your lookup array should be sorted in ascending order to ensure correct results.
| Requirement | Needed |
|---|---|
| Exact match | Sorting not required |
| Closest smaller | Sort ascending |
| Closest larger | Sort ascending |
Sorting improves accuracy and performance when working with approximate matches.
Combining XLOOKUP with IFERROR for Clean Output
Sometimes no suitable match exists. For example, when the lookup value is smaller than the smallest number.
You can handle this using IFERROR.
=IFERROR(XLOOKUP(E2, A2:A5, B2:B5, , -1), "No Match Found")This prevents confusing error messages and improves spreadsheet usability.
Using XLOOKUP for Tax Slabs and Commission Tables
One of the most popular uses of closest match is tax calculation.
Example Tax Slab
| Income Limit | Tax Rate |
|---|---|
| 0 | 0% |
| 250000 | 5% |
| 500000 | 20% |
| 1000000 | 30% |
To find the correct tax rate for an income of 620000:
=XLOOKUP(E2, A2:A5, B2:B5, , -1)This returns 20%, which is the correct slab.
Comparing XLOOKUP Closest Match vs VLOOKUP
| Feature | XLOOKUP | VLOOKUP |
|---|---|---|
| Closest match option | Built-in | Limited |
| Search direction | Any | Left to right only |
| Column index needed | No | Yes |
| Error handling | Easier | Harder |
| Formula readability | High | Medium |
XLOOKUP is more flexible and easier to maintain, especially for dynamic sheets.
Using XLOOKUP with Dynamic Arrays
In Excel 365, XLOOKUP supports dynamic arrays, meaning one formula can return multiple results automatically.
If you have a column of lookup values, you can apply one XLOOKUP formula and it spills results down instantly. This is helpful for bulk pricing, grade evaluation, and report automation.
Dynamic arrays reduce manual work and improve spreadsheet efficiency.
Best Practices for Closest Match with XLOOKUP
- Always sort lookup arrays when using closest match
- Use -1 for rounding down logic
- Use 1 for rounding up logic
- Wrap formulas with IFERROR for cleaner output
- Avoid mixing text and numbers in lookup ranges
- Keep lookup tables separate and well-labeled
Following these practices helps avoid calculation errors.
Final Thoughts
Learning how to use XLOOKUP to find the closest match can save time and reduce formula complexity. Whether you are calculating prices, commissions, grades, or taxes, XLOOKUP provides a clean and reliable solution.
By using the match_mode argument correctly, you can handle real-world scenarios where exact values do not exist. This makes your Excel models more flexible, accurate, and professional.
FAQs
Can XLOOKUP return the closest match instead of an exact match?
Yes, XLOOKUP can return the closest match by using the match_mode argument. Setting it to -1 returns the next smaller value, while 1 returns the next larger value.
Do I need to sort data when using XLOOKUP for closest match?
Yes, for closest match lookups, the lookup array should be sorted in ascending order. This ensures XLOOKUP returns accurate next smaller or next larger results.
What is the difference between match_mode -1 and 1 in XLOOKUP?
Using -1 finds the closest value that is less than or equal to the lookup value. Using 1 finds the closest value that is greater than or equal to the lookup value.
Can XLOOKUP closest match replace VLOOKUP approximate match?
Yes, XLOOKUP can fully replace VLOOKUP approximate match. It is more flexible, easier to read, and does not require column index numbers.
How do I avoid errors when no closest match exists?
You can wrap XLOOKUP inside IFERROR to display a custom message instead of an error when no suitable match is found.
Does XLOOKUP closest match work with dynamic arrays?
Yes, in Excel 365, XLOOKUP supports dynamic arrays. One formula can return closest match results for multiple lookup values automatically.
Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.