XLOOKUP was introduced as Microsoft's modern replacement for VLOOKUP, and it fixes several of VLOOKUP's most persistent limitations in one function: it can search in either direction, it doesn't require the lookup column to be the leftmost column, and it has a built-in argument for handling values that aren't found, without needing to wrap the whole formula in IFERROR.
This guide covers the XLOOKUP syntax in full, then shows how Turbo Excel Lookup's Lookup & Merge tab and Formula Generator both work with XLOOKUP-style logic, for people who want the same flexibility without writing the formula by hand.
- lookup_value - the value you're searching for.
- lookup_array - the single column or row to search within.
- return_array - the column or row to pull the result from; it can be anywhere relative to lookup_array, left or right.
- [if_not_found] - optional text or value returned instead of #N/A when nothing matches.
- [match_mode] - optional; controls exact, approximate, or wildcard matching.
- [search_mode] - optional; controls search direction, including searching from the last row first.
A basic exact-match formula looks like =XLOOKUP(A2, Catalog!A:A, Catalog!D:D, "Not Found"), which searches column A of Catalog for the value in A2 and returns the corresponding value from column D, or the text "Not Found" if nothing matches.
- Lookup and return columns can be in any order - no leftmost-column restriction
- Built-in if_not_found argument removes the need to wrap formulas in IFERROR
- References individual columns instead of a full table range, so inserting columns elsewhere doesn't shift a col_index_num
- Defaults to exact match, which is safer for beginners than VLOOKUP's default
Turbo Excel Lookup's Lookup & Merge tab already works the way XLOOKUP does under the hood - it doesn't care where your matching column sits relative to the columns you want returned, since you pick both independently from dropdowns of column headers. If you specifically need a live XLOOKUP formula written into a workbook, the Formula Generator tab builds the exact syntax, including the if_not_found argument, and can insert it into every row of a target column automatically.
The search_mode argument is the least-used of XLOOKUP's optional parameters, but it solves a specific problem well: finding the most recent match in a chronologically ordered list. Setting search_mode to -1 makes XLOOKUP scan from the last row to the first, so if a customer ID appears multiple times in an order log, the formula returns the most recent order rather than the earliest one, without needing to sort the data first.
This is functionally similar to VLOOKUP's Return Last Match limitation - VLOOKUP has no equivalent argument at all, and finding the last match with VLOOKUP typically requires an array formula or a helper column. Turbo Excel Lookup's Return Last Match option on the Lookup & Merge tab achieves the same outcome as search_mode -1 through a simple dropdown selection instead.
XLOOKUP is available in Excel for Microsoft 365 across Windows, Mac, and web, as well as Excel 2021 and Excel 2024 as standalone perpetual licenses. It is not available in Excel 2019, 2016, or earlier perpetual versions, which remain common in some organizations that haven't moved to a subscription license. Checking the Excel version before building a workbook around XLOOKUP avoids a frustrating discovery later that a shared file won't calculate correctly for some recipients.
XLOOKUP is built on Excel's dynamic array engine, which means a single XLOOKUP formula can spill multiple results into adjacent cells automatically if return_array spans more than one column - something VLOOKUP cannot do without separate formulas per column. This spilling behavior is worth understanding before building a formula that references a multi-column return_array, since Excel will need empty space below and to the right of the formula cell for the results to spill into without triggering a #SPILL! error.
XLOOKUP fixes several of VLOOKUP's most persistent annoyances in one function, provided your Excel version supports it. For anyone building new workbooks on Microsoft 365 or a recent Excel version, it's generally worth defaulting to XLOOKUP over VLOOKUP going forward.
Is XLOOKUP available in every version of Excel?
XLOOKUP requires Microsoft 365 or Excel 2021 and later; it isn't available in Excel 2019 or earlier, where VLOOKUP or INDEX/MATCH are still needed.
Does XLOOKUP need the table sorted?
No, XLOOKUP works correctly on unsorted data by default, unlike VLOOKUP's approximate match mode.
Can XLOOKUP replace both VLOOKUP and HLOOKUP?
Yes, since XLOOKUP works on both columns and rows, a single function can perform the equivalent of both a vertical and a horizontal lookup depending on how the ranges are oriented.
What if I need XLOOKUP-style logic but I'm on an older Excel version?
Turbo Excel Lookup runs independently of your Excel version since it's a standalone Windows app, so its flexible, direction-agnostic matching works the same whether your Excel supports XLOOKUP natively or not.
What does search_mode -1 do in XLOOKUP?
It reverses the search direction, scanning from the last row to the first, which is useful for finding the most recent match in chronological data.
Is there a VLOOKUP equivalent to XLOOKUP's search_mode?
Not directly - VLOOKUP always searches top-down with no built-in option to reverse direction, which is one of the gaps XLOOKUP was designed to close.
Does XLOOKUP work the same on Mac as it does on Windows?
Yes, XLOOKUP behaves identically across Windows, Mac, and Excel for the web, as long as the installed version supports it.
What does it mean for an XLOOKUP formula to "spill"?
It means a single formula can return multiple values that fill adjacent cells automatically, which happens when return_array spans more than one column - Excel needs empty space nearby for this to work without an error.
Ready to stop fighting #N/A errors and match your spreadsheets visually?