How to Use XLOOKUP in Excel: The Ultimate Guide (Goodbye VLOOKUP!)

If you’ve spent any time working with data in Microsoft Excel, you’ve probably experienced that specific brand of frustration when a VLOOKUP breaks. You insert a new column into your dataset, and suddenly half your spreadsheet lights up with dreaded #REF! errors. Or maybe you spent five minutes manually counting columns from left to right—1, 2, 3, 4, 5… wait, was that column E or F?

For decades, Excel users had to choose between the rigid limitations of VLOOKUP or the slightly intimidating syntax of INDEX and MATCH.

Then Microsoft gave us XLOOKUP.

Whether you’re managing inventory, building financial models, or just trying to organize a customer list, learning XLOOKUP is one of the highest-return Excel skills you can master today. In this guide, we’ll break down how XLOOKUP works in plain English, walk through real-world examples step by step, and cover the most common questions.

Caption: Say goodbye to broken formulas and column counting—XLOOKUP makes data retrieval effortless.

Why XLOOKUP is a Game Changer

Before we dive into syntax, let’s quickly talk about why XLOOKUP has completely rendered older lookup functions obsolete:

1. It looks in any direction: Unlike VLOOKUP, which can only look to the right of your search column, XLOOKUP can search left, right, up, or down without complaining.
2. Exact matches by default: No more accidentally leaving off , FALSE at the end of your formula and pulling incorrect approximate matches.
3. Built-in error handling: You don’t need to wrap your formula in IFERROR() anymore. XLOOKUP has a built-in argument to handle missing data cleanly.
4. Resilient to column changes: You can insert, delete, or reorder columns in your table without breaking your XLOOKUP formulas.
5. Returns dynamic arrays: XLOOKUP can pull an entire row or multiple columns of data with just a single formula.

The Anatomy of an XLOOKUP Formula

At first glance, XLOOKUP might look a bit intimidating because it can take up to six arguments. But here’s the secret: you only need the first three to make it work 90% of the time.

Here is the full structure:

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

Let’s translate that into plain human English:

• lookup_value (Required): What are you searching for? (e.g., an Employee ID, a product code, or a name).
• lookup_array (Required): Where should Excel search for it? (The column or row containing your search keys).
• return_array (Required): What data do you want back? (The column or row containing the information you want to retrieve).
• [if_not_found] (Optional): What should Excel display if it doesn’t find a match? (e.g., “Not Found” or 0).
• [match_mode] (Optional): Exact match, approximate match, or wildcard search? (Defaults to exact match: 0).
• [search_mode] (Optional): Search top-to-bottom (1) or bottom-to-top (-1)?

Step-by-Step Examples: From Simple to Advanced

Let’s look at how this plays out in real scenarios. Imagine we have the following sample dataset of employee records:

Example 1: The Standard Left-to-Right Lookup

Suppose you want to find the Department for employee EMP-103.

Here is how you write the formula:

=XLOOKUP(“EMP-103”, A2:A5, C2:C5)

How Excel executes this:

1. It takes “EMP-103”.
2. It searches down column A2:A5 until it finds “EMP-103” in row 4.
3. It jumps across to row 4 in C2:C5 and returns “Logistics”.

Example 2: The “Reverse” Lookup (Looking to the Left)

With VLOOKUP, if your lookup column was on the right and your target data was on the left, you were out of luck (unless you rearranged your entire table or used INDEX/MATCH).

XLOOKUP doesn’t care about column order at all.

Suppose you have an employee’s Name (“Ellen Ripley”) and you want to find her Employee ID (which is located to the left in Column A):

=XLOOKUP(“Ellen Ripley”, B2:B5, A2:A5)

Result: EMP-103

That’s it. No special tricks, no complex nested functions. It just works.

Caption: Clean formulas keep dashboards reliable and easy for your teammates to maintain.

Example 3: Built-In Error Handling (No More #N/A)

If you search for an item that doesn’t exist using standard lookup functions, Excel throws an ugly #N/A error message.

With XLOOKUP, we can use the 4th argument—[if_not_found]—to handle missing values gracefully without cluttering our sheet with IFERROR().

If someone types an invalid ID like EMP-999:

=XLOOKUP(“EMP-999”, A2:A5, C2:C5, “Employee Not Found”)

Result: Employee Not Found

If the employee exists, it displays their department. If not, it prints your custom text.

Example 4: Returning Multiple Columns at Once

Here’s a feature that blows traditional lookup formulas out of the water. What if you want to pull Name, Department, and Salary all at once using a single formula?

Instead of selecting a single column for your return_array, select all three!

=XLOOKUP(“EMP-102”, A2:A5, B2:D5)

Result:

Excel automatically spills the results across three adjacent cells:

You only type the formula once in the first cell, and Excel’s dynamic array engine handles the rest.

Example 5: Wildcard Matching for Partial Hits

Sometimes you don’t know the exact string you are searching for. Maybe you only remember part of a product name or an employee’s last name.

To enable wildcard searches in XLOOKUP, set the 5th argument ([match_mode]) to 2.

• * represents any number of characters.
• ? represents a single character.

If you want to find the salary of anyone whose last name is “Ripley”:

=XLOOKUP(“*Ripley*”, B2:B5, D2:D5, “Not Found”, 2)

Result: $98,000

Common Pitfalls (And How to Avoid Them)

Even though XLOOKUP is user-friendly, there are a couple of small mistakes that can trip you up:

1. Mismatched Range Sizes: Your lookup_array and return_array must be the same height (or width). If your lookup range is A2:A10 (9 rows) and your return range is C2:C15 (14 rows), Excel will return a #VALUE! error.
2. Forgetting Absolute References when Dragging: If you plan on copying your XLOOKUP formula down a column of hundreds of rows, make sure to lock your range arrays with dollar signs ($A$2:$A$5), or convert your data range into a native Excel Table (Ctrl + T) where range names update automatically!

Frequently Asked Questions (FAQ)

1. Is XLOOKUP available in all versions of Excel?

No. XLOOKUP was introduced in Excel for Microsoft 365, Excel 2021, and modern web versions of Excel. If you are using Excel 2019, 2016, or older standalone versions, XLOOKUP will not be available. In those older versions, you’ll still need to use VLOOKUP or INDEX/MATCH.

2. Is XLOOKUP faster than VLOOKUP or INDEX/MATCH?

Yes! XLOOKUP is generally faster and more memory-efficient, especially on large workbooks. Because it only evaluates the specific lookup and return ranges you select (rather than entire multi-column table arrays), it saves computational power during recalculations.

3. What happens if there are duplicate matching values in my dataset?

By default, XLOOKUP searches from top to bottom and returns the first match it finds. However, if you want to find the last match in your list (for example, the most recent transaction date), you can set the 6th argument (search_mode) to -1.

=XLOOKUP(“EMP-101”, A2:A10, D2:D10, “Not Found”, 0, -1)

4. Can XLOOKUP work across different worksheets or workbooks?

Absolutedly! Just like any other Excel formula, you can select lookup and return arrays located in other tabs or even in entirely different Excel files.

5. Can I use XLOOKUP for horizontal lookups (like HLOOKUP)?

Yes. XLOOKUP seamlessly replaces both VLOOKUP (vertical) and HLOOKUP (horizontal). If your data is laid out horizontally in rows instead of columns, simply select horizontal ranges for your lookup_array and return_array (e.g., =XLOOKUP(B1, A2:E2, A5:E5)).

The Verdict: Time to Upgrade Your Excel Workflow

If you’ve been relying on VLOOKUP out of habit, making the switch to XLOOKUP will save you hours of debugging broken ranges and counting columns. It is cleaner, faster, less error-prone, and far more flexible.

Give it a try in your next spreadsheet project! Once you get used to its simple three-argument setup, you’ll never want to go back.