XLOOKUP in Excel
How the formula works, a worked example, and how it compares to VLOOKUP
XLOOKUP in Excel searches a specified column for a value and returns a corresponding value from a separate column, without requiring the two columns to be part of a single combined range. It was introduced to address several long-standing limitations of VLOOKUP.
This page covers how the formula works, a complete worked example with an order table, and the practical differences from VLOOKUP worth knowing.
App Screenshots
How XLOOKUP Works, Step by Step
- Excel scans lookup_array for a value matching lookup_value.
- Once a matching position is found, Excel notes its row position within that array.
- It then looks at the same relative row position within return_array.
- The value at that position in return_array is returned as the result.
A Complete Worked Example
This order table is used to find the Status of ORD-501:
| Order ID | Customer Name | City | Status |
|---|---|---|---|
| ORD-501 | Rohan Gupta | Chennai | Shipped |
| ORD-502 | Meera Pillai | Bengaluru | Pending |
| ORD-503 | Arjun Nair | Hyderabad | Delivered |
Key Differences From VLOOKUP
XLOOKUP doesn't require the lookup column to be leftmost - return_array can sit anywhere relative to lookup_array, including to its left. It also defaults to an exact match, unlike VLOOKUP which defaults to an approximate one, making XLOOKUP arguably safer for beginners.
Returning an Entire Row Instead of One Column
Selecting a multi-column return_array, such as B2:D4, returns all three columns at once when the formula is entered as a spilling array formula, which VLOOKUP cannot do in a single formula.
An Alternative for Whole Datasets
Turbo Excel VLookup applies this same matching logic across an entire order list at once - you choose the matching columns and the columns to return through dropdown menus, without writing or copying the formula manually.
Why XLOOKUP Formulas Rarely Break From Inserted Columns
Because lookup_array and return_array are selected independently rather than counted from a combined range, inserting a new column between them doesn't shift col_index_num the way it would with VLOOKUP - Excel automatically adjusts both named ranges to keep pointing at the correct data.
This is one of the more practical advantages of XLOOKUP in a spreadsheet that's edited frequently, since it removes a whole category of silent, hard-to-spot errors.
Building the Habit of Testing With One Row First
Before copying an XLOOKUP formula down an entire column, testing it against a single known row and manually verifying the result catches setup mistakes - like mismatched range sizes - before they propagate across a whole dataset.
Choosing Between Exact and Approximate Match Modes
XLOOKUP's optional fifth argument, match_mode, defaults to an exact match, but it also supports approximate matches - either the next smaller or next larger value when no exact match exists - by passing -1 or 1 instead of the default 0.
This is useful for scenarios like matching a score against grade thresholds, where an exact match won't always exist but the closest applicable bracket still needs to be found.
Frequently Asked Questions
Does XLOOKUP require the return column to be to the right?
No, return_array can be positioned anywhere relative to lookup_array, including to its left.
What match mode does XLOOKUP default to?
An exact match, unlike VLOOKUP, which defaults to an approximate match unless FALSE is specified.
Can XLOOKUP return multiple columns from one formula?
Yes, by selecting a multi-column return_array, XLOOKUP can spill several columns of results at once.
Is XLOOKUP available in all versions of Excel?
No, it requires Microsoft 365 or Excel 2021 or later; older perpetual versions don't support it.
Does XLOOKUP work across different worksheets?
Yes, lookup_array and return_array can each reference ranges on other sheets.
What's a faster option for matching a whole order list?
Turbo Excel VLookup matches an entire dataset in a single action instead of one formula per order.
What's a fast way to check what a formula is actually returning?
Selecting part of the formula in the formula bar and pressing F9 shows its calculated value directly, without changing the formula.
Can trailing spaces in a cell cause a lookup to fail?
Yes, an extra space at the end of a value is invisible but prevents an exact match; TRIM() around the lookup value often fixes this.
Does the order of rows in the source data matter for an exact match?
No, an exact match search works regardless of row order; only an approximate match requires sorted data.
Is it safe to have duplicate values in the lookup column?
A lookup formula will only return the first matching row it finds, so duplicates can silently return the wrong record if not accounted for.
Related guides and tools
This guide is part of TurboSoft’s Excel lookup and matching resources. Turbo Excel Lookup handles all of these tasks offline on Windows.