For decades, VLOOKUP was the go-to tool for data retrieval in Excel. However, it had significant limitations, such as only searching to the right and requiring sorted data for approximate matches. Enter XLOOKUP—the most powerful and flexible lookup function available in modern Excel (Office 365 and Excel 2021+). In this guide, we will show you how to master XLOOKUP to streamline your workflow.

Step 1: Understand the XLOOKUP Syntax

Before diving into examples, it is essential to understand the basic arguments of the formula. Unlike older functions, XLOOKUP only requires three pieces of information to work, though it offers six in total:

• lookup_value: What you are looking for.
• lookup_array: Where to find that value (the column or row).
• return_array: What data you want to bring back.
• if_not_found: (Optional) What to show if no match is found.
• match_mode: (Optional) Specify exact, next smaller, or next larger match.
• search_mode: (Optional) Search from top-to-bottom or bottom-to-top.

Step 2: Perform a Basic Exact Match

The most common use for XLOOKUP is finding a value in one column and returning a corresponding value from another. To do this, type the following formula:

=XLOOKUP(A2, D:D, E:E)

In this example, Excel looks for the value in cell A2 within column D and returns the result from the same row in column E. Unlike VLOOKUP, you don't need to count column index numbers.

Step 3: Search to the Left

One of the biggest advantages of XLOOKUP is its ability to search to the left of the lookup column. Because you select the lookup_array and return_array independently, the return column can be anywhere in your spreadsheet.

=XLOOKUP("EmployeeID", C:C, A:A)

This formula finds the ID in column C and pulls the name from column A, something VLOOKUP cannot do without complex helper columns or the INDEX/MATCH combo.

Step 4: Handle Errors with "If Not Found"

In the past, you had to wrap your formulas in IFERROR to avoid ugly #N/A results. XLOOKUP has this built-in. Simply add a text string as the fourth argument:

=XLOOKUP(A2, D:D, E:E, "Not Found")

If the value in A2 does not exist in the range, Excel will gracefully display "Not Found" instead of an error code, keeping your data clean for presentations.

Step 5: Use XLOOKUP for Horizontal Lookups

XLOOKUP replaces both VLOOKUP and HLOOKUP. If your data is organized in rows rather than columns, you can use it the exact same way. Just select the horizontal rows for your arrays:

=XLOOKUP("January", 1:1, 2:2)

This searches for "January" in the first row and returns the value from the second row, making it a truly universal tool for spreadsheet productivity.

Step 6: Master Two-Way Lookups

You can nest one XLOOKUP inside another to perform a 2D matrix search (finding the intersection of a specific row and column). Use this structure:

=XLOOKUP(Row_Value, Row_Range, XLOOKUP(Column_Value, Column_Range, Data_Matrix))

This is incredibly useful for dynamic dashboards where you need to pull data based on both a Date and a Product Category simultaneously.

💡 Pro Tip: Keep your software updated to avoid these issues in the future.