How to Use XLOOKUP in Excel

XLOOKUP is generally easier to use correctly than VLOOKUP once you understand its two core ranges - lookup_array and return_array - since they're selected independently rather than being counted from within a single combined table_array. This walkthrough covers the process from a blank cell to a working formula, including the optional arguments most people skip at first.

Once the standard process is clear, the final section shows how Turbo Excel Lookup performs an equivalent lookup for anyone who wants the same result without an Excel version requirement or a formula to maintain.

1. Click the destination cell for your result.
2. Type =XLOOKUP( and click the cell containing the value to search for.
3. Add a comma, then select only the single column or row you're searching within (not the whole table).
4. Add a comma, then select the single column or row you want the result pulled from.
5. Optionally add a comma and type text in quotes for what should show if nothing matches.
6. Close the parenthesis and press Enter.

Unlike VLOOKUP, there's no column-counting step - you point directly at the return column rather than calculating its position within a combined range, which removes a common source of off-by-one errors.

The if_not_found argument is worth using by default, since it replaces #N/A with something more useful, like "Check spelling" or a blank string. The match_mode argument is useful when you need wildcard matching (using * and ?) or when exact match should fail back to the next smallest value instead of erroring outright. The search_mode argument is rarely needed, but is useful for finding the last occurrence of a duplicated key rather than the first.

Since XLOOKUP requires a recent Excel version, anyone on Excel 2019 or earlier, or working with a file that needs to open reliably across mixed Excel versions, doesn't have this option available natively. Turbo Excel Lookup's Lookup & Merge tab offers the same column-order flexibility and if_not_found-style Fill Value setting regardless of which Excel version is eventually used to open the resulting file, since the matching itself happens outside Excel entirely.

XLOOKUP is generally efficient, but referencing entire columns (A:A) rather than a bounded range (A2:A10000) still means Excel considers every row up to the sheet's maximum, which can add unnecessary recalculation overhead in a workbook with many such formulas. Using a bounded range, or better yet an Excel Table with structured references that expand automatically as data grows, keeps performance predictable without sacrificing the convenience of not having to manually update a fixed row count.

For very large lookups - hundreds of thousands of rows - a dedicated tool tends to outperform any worksheet formula regardless of range boundaries, since Turbo Excel Lookup's fast match modes use vectorized operations rather than per-cell formula recalculation, which is where the practical performance ceiling of any live formula is eventually reached.

The biggest adjustment for someone moving from VLOOKUP to XLOOKUP is trusting that lookup_array and return_array can be selected completely independently, without needing them to be part of one continuous, pre-planned range. It can feel unfamiliar at first to select two ranges that aren't adjacent, but this independence is exactly what removes VLOOKUP's leftmost-column restriction, and it becomes natural after a few uses.

A practical extension once the basic XLOOKUP formula is working is pairing it with a Data Validation dropdown on the lookup_value cell, restricting entries to only values that actually exist in lookup_array. This prevents typos in the lookup cell itself from ever reaching the formula, catching a common source of unexpected "not found" results before they happen rather than troubleshooting them after the fact.

XLOOKUP's independent range selection removes several of VLOOKUP's most common setup mistakes by design, making it a noticeably friendlier function for anyone learning lookups for the first time, provided the Excel version in use actually supports it.

As a final practical habit, testing a new XLOOKUP formula against at least one row you already know the correct answer for, before trusting it across a full column, catches the same class of setup mistakes that trip up VLOOKUP, even though XLOOKUP's structure makes them somewhat less likely to begin with.