To find the closest match with XLOOKUP in Excel, use the match_mode argument set to -1 (exact match or next smaller) or 1 (exact match or next larger). For example, =XLOOKUP(D2, A2:A10, B2:B10, , -1) returns the value for the largest lookup value that is less than or equal to the search key.
What XLOOKUP’s Match Mode Actually Does
XLOOKUP’s fifth argument, match_mode, controls how it handles non-exact matches. The default is 0 for exact match. Setting it to -1 finds the closest value that is less than or equal to the lookup value. Setting it to 1 finds the closest value that is greater than or equal to the lookup value. This is similar to Excel’s older VLOOKUP with range_lookup set to TRUE, but XLOOKUP works in any direction and doesn’t require the lookup column to be the first column.
Important: For -1 or 1 to work correctly, the lookup array must be sorted in ascending order (for -1) or descending order (for 1). If the data is not sorted, XLOOKUP may return incorrect results, not just an error.
How to Use XLOOKUP for Closest Match (Step-by-Step)
Assume you have a list of product prices in column A (sorted ascending) and product names in column B. You want to find the product whose price is closest to a given budget without exceeding it.
- Place your search value (e.g., budget) in cell D2.
- In cell E2, enter the formula:
=XLOOKUP(D2, A2:A10, B2:B10, , -1) - Press Enter. The result is the product name from the row where the price is the largest value ≤ D2.
If you want the closest value that is above the search key (e.g., find the next higher price), use 1 instead: =XLOOKUP(D2, A2:A10, B2:B10, , 1). Note that for 1, the lookup array must be sorted in descending order.
Choosing Between -1 and 1 (Which One to Use)
The choice depends on your business logic. If you are pricing a product and need to stay under a customer’s budget, use -1 (next smaller). If you are calculating shipping costs based on weight brackets and need the rate for the next higher weight tier, use 1 (next larger).
For example, a shipping table might have weight thresholds in column A and rates in column B. If a package weighs 3.2 kg, and the table has 3 kg and 4 kg, -1 would give the 3 kg rate (underestimating), while 1 would give the 4 kg rate (correct for shipping). Always consider which side is the correct one for your use case.
Common Pitfalls and How to Avoid Them
- Unsorted data: Even a single unsorted row can break the match. Sort the lookup array in the required order.
- Using -1 with descending data: This returns the smallest value that is ≥ the search key, which is not what you want. Keep the sort order consistent with the match mode.
- Forgetting the if_not_found argument: If no match exists (e.g., search value is smaller than the smallest value in the array), XLOOKUP returns #N/A. Use the fourth argument to provide a friendly message:
=XLOOKUP(D2, A2:A10, B2:B10, "No match", -1) - Mixing data types: If the lookup array contains text and numbers, XLOOKUP may not find a match. Ensure the lookup value and array are the same data type.
XLOOKUP vs. VLOOKUP for Closest Match
VLOOKUP with TRUE as the last argument also finds the closest match, but it has limitations: it can only look left-to-right, requires the lookup column to be the first column, and only works with -1 logic (next smaller). XLOOKUP is more flexible because it can return values from any column, work with arrays, and handle both directions. If you are using Excel 2021 or Microsoft 365, XLOOKUP is the better choice. If you are on an older version, VLOOKUP is your only option, but you must sort the data ascending.
Real-World Example: Tax Bracket Lookup
Suppose you have tax brackets in column A (income thresholds, ascending) and tax rates in column B. To find the tax rate for an income in D2, use:
=XLOOKUP(D2, A2:A6, B2:B6, , -1)
If D2 is 45,000 and the thresholds are 0, 10,000, 20,000, 40,000, 80,000, the formula returns the rate for 40,000, which is the correct bracket. This works because the data is sorted ascending.
Frequently Asked Questions
Can XLOOKUP find the closest match without sorting?
No. The -1 and 1 match modes rely on a sorted array. If you cannot sort your data, you need to use an array formula like INDEX with MATCH and MIN to find the absolute closest value, but that is more complex and not a native XLOOKUP feature.
What if there are ties? Which one does XLOOKUP return?
When multiple values are equally close, XLOOKUP with -1 or 1 returns the first one it encounters in the sorted order. For -1, that is the largest value that is ≤ the lookup value; for 1, it is the smallest value that is ≥ the lookup value. If you need a specific tie-breaker, you must add a helper column.
Does XLOOKUP work in Excel for Mac or Excel for the web?
Yes. XLOOKUP is available in Excel for Microsoft 365 on Windows, Mac, and Excel for the web. The syntax is identical. However, older versions of Excel (2019 and earlier) do not support XLOOKUP; you would need to use VLOOKUP or INDEX/MATCH.
Can I use XLOOKUP to find the closest date?
Yes, as long as the dates are stored as real Excel dates (not text) and are sorted. The same -1 or 1 logic applies. For example, to find the last event before a given date, use -1 with dates sorted ascending.
What is the difference between match_mode -1 and 0?
0 requires an exact match; if none is found, XLOOKUP returns the if_not_found value or #N/A. -1 finds the closest value that is less than or equal to the lookup value, even if it is not an exact match. Use 0 when you only want exact matches, and -1 or 1 when you need the nearest fallback.
How do I find the absolute closest match (either higher or lower)?
XLOOKUP alone cannot do this directly. You would need to combine INDEX, MATCH, and MIN with an array formula, or use LET and ABS to calculate the minimum absolute difference. For example, with lookup value in D2 and data in A2:A10, the formula =INDEX(B2:B10, MATCH(MIN(ABS(A2:A10-D2)), ABS(A2:A10-D2), 0)) entered as an array formula (Ctrl+Shift+Enter in older Excel) returns the value from the row with the smallest absolute difference.
Related
- Excel Sum Only Numbers Ignore Text in Cell?
- Excel Sum Showing Zero? Fixes That Work
- Excel Spreadsheet Zoom Lock: How to Freeze Zoom Level
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel MIN Ignore Errors: Formulas and Common FixesTo find the smallest number in a range while ignoring error cells, use =AGGREGATE(5,6,A2:A10). The 5 selects MIN,…
- Filter by Two Criteria in Excel: A Practical GuideTo filter by two criteria in Excel, turn on filters with Data > Filter, then set a condition…
- Excel INDEX and MATCH Lookup: How to Use ItThe short answer: INDEX and MATCH is a pair of Excel functions that together perform a two-way lookup,…
- Excel Max Value Based on Criteria: Formula GuideTo find the maximum value in Excel based on one or more criteria, use MAXIFS (for Excel 2019…
- VLOOKUP in Excel Explained: Syntax, Examples, and LimitsVLOOKUP in Excel searches for a value in the first column of a table and returns a related…
- Excel XLOOKUP Between Two Worksheets: ExamplesIf your lookup value is on one worksheet and the data to search is on another, XLOOKUP can…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.