XLOOKUP in Excel: A Guide for Finance Professionals

XLOOKUP is Excel's function for finding something in one range and returning a matching item from another. Microsoft's support page describes it as a way to look in one column for a search term and return a result from the same row in another column. Finance teams use lookups constantly, for example to bring an account name, a department or a tax rate next to a code. This guide takes the XLOOKUP angle, based on the function's own Microsoft Support page, which shows no publication or update date, so check the current page for your version.

If you already rely on the older function, our complete guide to VLOOKUP for accountants covers it in depth; here we focus on what XLOOKUP does, and how the page says it differs.

The syntax

The page gives the syntax as:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

• lookup_value (required): the value to search for. The page says that if it is omitted, XLOOKUP returns blank cells it finds in lookup_array.
• lookup_array (required): the array or range to search.
• return_array (required): the array or range to return.
• if_not_found (optional): text returned when no valid match exists. If you leave it out, the result is #N/A.
• match_mode (optional): how matching works.
• search_mode (optional): the order in which the search runs.

The key structural point is that the search range and the return range are separate arguments. There is no column index number to count.

match_mode: exact, next smaller, next larger, wildcard

According to the page, match_mode takes these values:

• 0: exact match; returns #N/A if none is found. This is the default.
• -1: exact match, or the next smaller item if none is found.
• 1: exact match, or the next larger item if none is found.
• 2: wildcard match, where the characters *, ? and ~ have special meaning.

The default being an exact match is helpful for finance work, where you usually want a code to match exactly or not at all. With XLOOKUP, the page's default is exact and you opt in to approximate matching deliberately, whereas the VLOOKUP equivalent the page shows has to specify FALSE to get an exact match.

The page includes an example of an approximate match used to find a tax rate (using match_mode 1 and an if_not_found value). Illustrative example (made-up bands): a table lists income thresholds in one column and rates in another; an approximate-match lookup finds the band a given amount falls into. Any real rate or threshold must come from a current official source such as Revenue, not from an Excel example.

search_mode: direction and binary search

The page lists these search_mode values:

• 1: search from the first item to the last. Default.
• -1: reverse search, from the last item to the first.
• 2: binary search, requiring lookup_array sorted in ascending order.
• -2: binary search, requiring lookup_array sorted in descending order.

The page warns that if the array is not sorted for a binary search, invalid results will be returned. The page says only that invalid results will be returned; it does not mention an error message, so check the sort order yourself. Our suggestion: leave search_mode alone unless you have a reason, and if you use binary search, check the sort order first.

A reverse search (-1) can be useful when a code appears several times and you want the most recent entry, assuming the list is in date order. That use is our suggestion; the page documents the mode itself.

Handling "not found" cleanly

The page shows an example that adds an if_not_found message, "Employee not found". In a reconciliation, a visible message is better than a hidden error because it tells the reader which item failed. The fourth argument, if_not_found, is what supplies this message.

Illustrative example (made-up data): =XLOOKUP(E2,B2:B7,C2:C7,"Not found") looks up the code in E2 within B2:B7 and returns the matching entry from C2:C7, or the text "Not found" if there is no match. The ranges are invented for illustration.

Differences from VLOOKUP and HLOOKUP

The page makes three comparisons:

• XLOOKUP takes separate lookup and return arrays, whereas VLOOKUP uses one table plus a column index number. The page's equivalent VLOOKUP example uses FALSE for an exact match.
• XLOOKUP can return multiple columns from one formula. One page example returns both an employee name and a department.
• XLOOKUP's lookup column can be to the right of the return column, whereas the page says VLOOKUP can only look from left to right.

The page also says XLOOKUP can replace HLOOKUP, showing a nested XLOOKUP for a two-way (vertical and horizontal) match that it likens to INDEX and MATCH together. And it shows that because XLOOKUP can return a range, you can use two XLOOKUPs on either side of a colon inside SUM to total values between two selections.

Availability: check before you share a workbook

The page's Applies To list covers Excel for Microsoft 365 (Windows and Mac), Excel 2024, Excel 2021 and the Excel mobile apps; it also lists Excel 2019 and 2016, which sits oddly beside its own note that XLOOKUP is not available in Excel 2016 and Excel 2019. Treat the explicit note as the safer reading, and note the page says workbooks may still contain XLOOKUP if they were created in a newer version. For a Dublin finance team sharing files with clients or auditors, that matters: a workbook built with XLOOKUP may not calculate for someone on an older version. A practical approach is to check which versions your recipients use, or to keep a VLOOKUP version of critical files.

Getting data ready for lookups

A lookup is only as good as the keys it matches. Stray spaces, codes stored as text in one list and numbers in another, and duplicates in the lookup column all cause misses or the wrong match. These are general data-quality points rather than something the Microsoft page states. When the same cleaning is needed every month, it is worth looking at an automated approach such as Power Query for accountants.

If you are preparing for a formal Excel exam, our overview of the Microsoft Excel certification exam explains how skills like this fit.

Learnsignal's offices are off Baggot Street in Dublin, and we offer in-person training from there. For current details, see our CPD pages.

Frequently asked questions

What are the required arguments of XLOOKUP?

Three: lookup_value, lookup_array and return_array. The other three, if_not_found, match_mode and search_mode, are optional, according to Microsoft's page.

What is the default match mode?

0, an exact match, which returns #N/A if nothing is found unless you supply an if_not_found value.

Can XLOOKUP look to the left?

Yes. The page says the lookup column can be to the right of the return column, unlike VLOOKUP, which it says can only look from left to right.

Is XLOOKUP available in every Excel version?

No. The page says it is not available in Excel 2016 and Excel 2019. Its Applies To list names Microsoft 365, 2024, 2021 and the mobile apps (and, inconsistently, 2019 and 2016), so check your own version.

XLOOKUP's separate lookup and return arrays, exact-match default and built-in not-found handling make lookups easier to read, provided everyone who opens the file has a version that supports it.