How to Use XLOOKUP in Excel to Find Data Quickly

What is XLOOKUP?

For decades, VLOOKUP was the most famous formula in Microsoft Excel, used by millions to search for a value in one column and return a corresponding value from another. However, it had severe limitations—it could only search from left to right, and adding or removing columns often broke the formula. XLOOKUP is Microsoft’s modern, incredibly powerful replacement. It solves every problem VLOOKUP had, while being easier to learn and use.

Why You Should Stop Using VLOOKUP

If you are still using VLOOKUP, transitioning to XLOOKUP will immediately improve your spreadsheets. XLOOKUP is superior because:

It searches in any direction: Unlike VLOOKUP, which demands the lookup value be in the first column, XLOOKUP can search right-to-left or left-to-right effortlessly.
It does not break when columns change: XLOOKUP uses specific cell ranges rather than a rigid column index number. If you insert a new column into your data table, XLOOKUP automatically adjusts and keeps working.
Built-in error handling: You no longer need to wrap your formula in IFERROR() to hide ugly “#N/A” errors. XLOOKUP has an “if not found” parameter built directly into the formula.
It defaults to an exact match: VLOOKUP frustratingly defaults to an approximate match (requiring you to type “FALSE” at the end). XLOOKUP defaults to an exact match, which is what users want 99% of the time.

The XLOOKUP Syntax Explained

The basic structure of an XLOOKUP formula looks like this:

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

lookup_value: What are you looking for? (e.g., an employee ID in cell A2).
lookup_array: Where should Excel search for that value? (e.g., the column containing all employee IDs).
return_array: What column contains the answer you want back? (e.g., the column containing employee names).
[if_not_found] (Optional): What should Excel display if the value does not exist? (e.g., “Not Found”).

How to Write an XLOOKUP Formula

Let’s walk through a practical example. Imagine you have a large table of inventory. Column D contains Product IDs, and Column B contains the Product Names (notice the name is to the left of the ID). You want to type a Product ID into cell G2 and have the Product Name automatically appear in cell H2.

Click on cell H2 (where you want the answer to appear).
Type =XLOOKUP(
Click on cell G2 (this is your lookup_value). Type a comma.
Highlight Column D in your data table (this is the lookup_array where the IDs live). Type a comma.
Highlight Column B in your data table (this is the return_array containing the names).
Type a comma, and then type “Product Not Found” in quotation marks (this is your if_not_found message).
Close the bracket ) and press Enter.

Your final formula will look like this:
=XLOOKUP(G2, D:D, B:B, "Product Not Found")

Advanced XLOOKUP Features

While the basic usage covers most scenarios, XLOOKUP has two additional, optional parameters for advanced users:

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

Match Mode: By default, it looks for an exact match (0). However, you can set it to find the next smaller item (-1) or the next larger item (1), which is perfect for tax brackets or grading systems.
Search Mode: By default, it searches from the top down (1). You can change this to search from the bottom up (-1) to find the most recent entry in a chronological list.

Common Mistakes to Avoid

Mismatched Array Sizes: The lookup_array (e.g., D2:D100) and the return_array (e.g., B2:B100) must be exactly the same size. If one is 99 rows and the other is 100 rows, the formula will return a #VALUE! error.
Compatibility Issues: XLOOKUP is available in Microsoft 365, Excel 2021, and Excel for the Web. If you send a workbook containing XLOOKUP to a colleague using an older version (like Excel 2016), the formula will not work. In those specific scenarios, you must still use VLOOKUP or INDEX/MATCH.

Next Steps

Open a test spreadsheet and try replacing your most complex VLOOKUP formula with XLOOKUP. You will immediately notice how much shorter, cleaner, and more resilient your spreadsheet becomes.