The XLOOKUP function is a versatile power tool in modern Excel that streamlines how data is retrieved from worksheets. Replacing older legacy functions like VLOOKUP, HLOOKUP, and the complex INDEX/MATCH combination, XLOOKUP provides a simplified yet robust way to find specific information within a range or table.

To use XLOOKUP, the formula requires at least three pieces of information: the value you are looking for, the column where that value lives, and the column containing the data you want to retrieve. A basic formula looks like this: =XLOOKUP(lookup_value, lookup_array, return_array). Unlike its predecessors, it defaults to an exact match and allows for searching in any direction—up, down, left, or right.

XLOOKUP is available to users with a Microsoft 365 subscription or those using Excel 2021 and later. It is also fully supported in Google Sheets.

What is the XLOOKUP function?

XLOOKUP is a modern search function designed to find a specific value in one range (the "lookup array") and return a corresponding value from a second range (the "return array"). It was introduced to solve the structural limitations of VLOOKUP, such as the inability to look to the left and the requirement to specify a column index number.

In practical terms, if you have a list of employee ID numbers and you need to find the name of the person associated with ID "E-105," XLOOKUP scans the ID column for "E-105" and then looks across to the Name column to pull the result.

Breaking down the XLOOKUP syntax

Understanding the anatomy of the XLOOKUP formula is essential for moving beyond basic searches. The function contains six arguments, though only the first three are mandatory.

Required Arguments

  1. lookup_value: This is the data point you are trying to find. It can be a hard-coded value (like "Apples"), a cell reference (like B5), or even the result of another formula.
  2. lookup_array: This is the specific range or column where Excel should search for the lookup_value. For example, if you are looking for an ID number, this would be the range of all ID numbers.
  3. return_array: This is the range or column from which you want to extract the result. It must be the same size as the lookup_array.

Optional Arguments

  1. [if_not_found]: A powerful feature that allows you to specify what Excel should show if no match is found. Instead of the dreaded #N/A error, you can display "No Match" or "0".
  2. [match_mode]: Controls how the search behaves:
    • 0: Exact match (the default).
    • -1: Exact match or the next smaller item.
    • 1: Exact match or the next larger item.
    • 2: Wildcard match (using * or ?).
  3. [search_mode]: Determines the direction and logic of the search:
    • 1: Search from first to last (the default).
    • -1: Search from last to first (useful for finding the most recent record).
    • 2 or -2: Binary search for sorted data (much faster for huge datasets).

How to perform a basic vertical lookup with XLOOKUP

For most users, the most common use case is replacing a standard VLOOKUP. Let's look at a typical scenario involving sales data.

Imagine you have a table where Column A contains Product SKUs and Column B contains Prices. You want to find the price for "SKU-99."

The Table Structure:

  • A2:A100: Product SKUs
  • B2:B100: Prices

The Formula: =XLOOKUP("SKU-99", A2:A100, B2:B100)

How it works: Excel looks into the range A2:A100. Once it finds "SKU-99," it identifies the row number. It then goes to the same row in the B2:B100 range and returns the price. This is significantly more intuitive than VLOOKUP because you don't have to count columns or worry about whether the lookup column is the first one in your selection.

Why is XLOOKUP better than VLOOKUP?

After years of using VLOOKUP in financial audits, the shift to XLOOKUP feels like moving from a manual transmission to an automatic. There are four primary reasons why XLOOKUP is superior:

1. Leftwards Lookups

VLOOKUP can only search from left to right. If your "Key" is in the third column and you need data from the first column, VLOOKUP fails unless you restructure your data. XLOOKUP treats the lookup and return ranges as separate entities, meaning you can look to the left just as easily as you look to the right.

2. Default Exact Match

One of the most common mistakes in Excel history is forgetting to type FALSE at the end of a VLOOKUP formula. By default, VLOOKUP does an approximate match, which often leads to incorrect data retrieval. XLOOKUP assumes you want an exact match unless you tell it otherwise.

3. Structural Integrity

Because VLOOKUP uses a "column index number" (e.g., return the 3rd column), the formula breaks if you insert or delete a column within your table. XLOOKUP uses range references (e.g., C2:C100). If you insert a column, Excel automatically adjusts the range reference, keeping your formula intact.

4. Built-in Error Handling

In the past, to avoid #N/A errors, we had to wrap formulas like this: =IFERROR(VLOOKUP(...), "Not Found"). XLOOKUP includes the [if_not_found] argument directly in the syntax, making the formula cleaner and faster to write.

How to use XLOOKUP for horizontal searches

One of the most overlooked benefits of XLOOKUP is that it replaces HLOOKUP as well. If your data is organized in rows rather than columns, you can simply select horizontal ranges.

Example Scenario: You have a row of months (B1:M1) and a row of monthly revenue (B2:M2). You want to find the revenue for "October."

The Formula: =XLOOKUP("October", B1:M1, B2:M2)

XLOOKUP detects that the ranges are horizontal and adapts automatically. This unification of vertical and horizontal searching means users only need to master one function instead of two.

How to find the last item in a list using search mode

Finding the "last occurrence" of an item used to require complex array formulas or sorting data in reverse order. With XLOOKUP’s [search_mode] argument, this is now a standard feature.

Consider a log of customer support tickets. Column A has Customer Names, and Column B has the date of their last interaction. Since customers appear multiple times, you want to find the most recent date for "John Doe."

The Formula: =XLOOKUP("John Doe", A2:A500, B2:B500, "No records", 0, -1)

By setting the sixth argument to -1, Excel starts searching from the bottom of the list and moves upward. It finds the first "John Doe" it encounters (which is the last one in the list) and returns the corresponding date. This is an essential technique for tracking inventory shipments, stock prices, or any time-series data.

What happens when XLOOKUP cannot find a value?

Error handling is where XLOOKUP shines in terms of user experience. When a lookup fails in Excel, the default behavior is to return a #N/A error. This can be problematic if you are performing calculations on the results.

By utilizing the fourth argument, [if_not_found], you can control the output. For example:

=XLOOKUP(D2, A2:A100, B2:B100, "Check SKU")

If the value in D2 isn't found in column A, the cell will clearly state "Check SKU." You can also return a numeric 0 or a blank string "" to keep your spreadsheets looking clean. In our testing with large-scale datasets, this built-in handling reduced formula complexity and improved calculation speeds by avoiding nested IFERROR logic.

Using XLOOKUP to return multiple columns at once

One of the most powerful features of XLOOKUP in the "Dynamic Array" version of Excel is its ability to "spill" results. Instead of returning a single value, XLOOKUP can return an entire row or block of data.

Suppose you have a table with:

  • Column A: Employee ID
  • Column B: Name
  • Column C: Department
  • Column D: Salary

If you want to look up an ID and get the Name, Department, and Salary all at once, you can select multiple columns for the return_array.

The Formula: =XLOOKUP("E-105", A2:A100, B2:D100)

Because the return_array (B2:D100) spans three columns, XLOOKUP will fill the cell where the formula is placed and automatically "spill" the additional data into the two adjacent cells to the right. This ensures that all related data is pulled in a single, synchronized step.

How to use two-way lookups with nested XLOOKUP

A two-way lookup (or matrix lookup) is when you need to find a value at the intersection of a specific row and a specific column. Traditionally, this required the INDEX and MATCH combo. XLOOKUP can do this by nesting one function inside another.

Imagine a pricing grid where the rows are "Product Types" and the columns are "Regions" (North, South, East, West).

The Structure:

  • A2:A5: Product Types (e.g., Laptop, Tablet, Phone)
  • B1:E1: Regions (North, South, East, West)
  • B2:E5: The Price Matrix

To find the price for a "Tablet" in the "East" region, use this formula:

=XLOOKUP("Tablet", A2:A5, XLOOKUP("East", B1:E1, B2:E5))

The Logic:

  1. The inner XLOOKUP (XLOOKUP("East", B1:E1, B2:E5)) finds the column for "East" and returns that entire column (B2:B5).
  2. The outer XLOOKUP then looks for "Tablet" within the Product Types (A2:A5) and pulls the value from the column provided by the inner function.

This approach is significantly easier to read and debug than the old INDEX(..., MATCH(...), MATCH(...)) method.

Troubleshooting common XLOOKUP errors

Even with its improved design, you might encounter issues when using XLOOKUP. Here are the most common pitfalls:

1. #VALUE! Errors

The most frequent cause of the #VALUE! error is a mismatch in the size of your arrays. If your lookup_array is A2:A100 (99 rows) but your return_array is B2:B105 (104 rows), XLOOKUP won't know how to map the values. Always ensure both ranges have the same starting and ending row/column numbers.

2. #SPILL! Errors

This occurs when you are trying to return multiple columns (as described in the "Spill" section above), but there is existing data in the cells where the results should go. To fix this, clear the cells to the right or below your formula cell to give the data room to expand.

3. #N/A Errors

Despite the [if_not_found] argument, you might see #N/A if you haven't provided a value for that argument and the data is missing. Double-check your source data for leading or trailing spaces. A common issue is searching for "SKU-01" when the data actually contains "SKU-01 " (with a space). Using the TRIM function inside your lookup can often solve this: =XLOOKUP(D2, TRIM(A2:A100), B2:B100).

4. Version Compatibility

If you send a workbook containing XLOOKUP to someone using Excel 2016 or 2019, they will see a _xlfn.XLOOKUP prefix in the formula, and it will likely return a #NAME? error. If you are working in a mixed-version environment, you may still need to use INDEX/MATCH for backward compatibility.

Best practices for XLOOKUP in large datasets

When working with spreadsheets that exceed 100,000 rows, performance becomes a factor. Here are expert tips for maintaining speed:

  • Use Absolute References: Always lock your ranges with dollar signs (e.g., $A$2:$A$100) if you plan to drag the formula down. This prevents Excel from recalculating the range shifts for every cell.
  • Leverage Binary Search: If your data is sorted (e.g., by date or ID), use search_mode 2. This uses a binary search algorithm which is exponentially faster than the default linear search on massive tables.
  • Avoid Entire Column References: While =XLOOKUP(D2, A:A, B:B) is convenient, it forces Excel to look through over a million rows. Limiting the range to the actual data area (e.g., A2:A5000) can reduce calculation lag.

Frequently Asked Questions

Can XLOOKUP search for partial matches?

Yes. By setting the [match_mode] to 2, you can use wildcards. For example, XLOOKUP("App*", A2:A10, B2:B10, , 2) will find the first entry starting with "App," such as "Apples" or "Appliances."

Is XLOOKUP case-sensitive?

By default, no. XLOOKUP treats "APPLE" and "apple" as the same value. If you need a case-sensitive search, you must combine XLOOKUP with the EXACT function, though this requires a more advanced array formula approach.

Does XLOOKUP work across different sheets?

Absolutely. You can reference ranges in other worksheets or even other workbooks. Example: =XLOOKUP(A2, 'Sheet2'!A:A, 'Sheet2'!B:B).

Can I use XLOOKUP in Google Sheets?

Yes, Google Sheets fully supports XLOOKUP. The syntax is identical to Excel, making it easy to transition between the two platforms.

Summary of XLOOKUP benefits

XLOOKUP represents a significant leap forward in spreadsheet usability. By consolidating the features of VLOOKUP, HLOOKUP, and INDEX/MATCH into a single, flexible function, Microsoft has made data analysis more accessible.

Key takeaways for using XLOOKUP include:

  • Simplicity: No more counting columns or worrying about left/right orientation.
  • Safety: Default exact matches prevent common data errors.
  • Flexibility: Built-in error handling and search direction control offer professional-grade results with minimal effort.
  • Efficiency: Return multiple columns of data with a single formula using dynamic arrays.

Whether you are building a simple budget or managing a complex inventory system, mastering XLOOKUP is one of the most effective ways to improve your productivity in Excel. By replacing legacy functions with this modern alternative, you ensure your spreadsheets are more robust, easier to maintain, and less prone to errors.