3 Reasons Why XLOOKUP is Your New Best Friend

 

If you’ve been using Excel for a while, you’ve probably relied on VLOOKUP or INDEX/MATCH countless times. The days of fighting VLOOKUP’s limitations and INDEX/MATCH’s complexity are over. Meet XLOOKUP—Excel’s modern lookup function that’s flexible, simple, and powerful enough to replace both.

In this tutorial, we’ll share three reasons why XLOOKUP is your new best friend. You’ll see how it makes everyday lookup tasks faster, cleaner, and smarter—no workarounds required.

Syntax:

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

The XLOOKUP function is Excel’s modern lookup function that finds a value in one column (or row) and returns a related value from another column (or row). It’s designed to replace VLOOKUP and INDEX/MATCH by being simpler and more flexible.

Reason 1: XLOOKUP Can Look In Any Direction (Left Or Right)

VLOOKUP can only search to the right of your lookup column—if the lookup column isn’t the first column in your table array, the formula fails. INDEX/MATCH solves this but is harder to read because it requires nesting two functions, which can get messy with large datasets.

XLOOKUP flips this: it decouples the lookup range from the return range, so you can look left, right, up, or down without hassle.

Suppose you have a sales dataset and want to find a Product ID by searching for the Product Name.

VLOOKUP:

• Select a cell and insert the following formula.

=VLOOKUP(H5,A2:F23,1,FALSE)

This formula returns a #N/A error because VLOOKUP cannot look to the left (Product ID appears before Product Name in the sheet). To use VLOOKUP here, you’d need to rearrange columns—often not feasible because it can break your data structure and downstream formulas.

INDEX/MATCH:

• Select a cell and use the INDEX/MATCH combo.

=INDEX(A2:A23, MATCH(H8, B2:B23, 0))

This formula returns the Product ID (P006). It works, but you must specify both ranges and ensure MATCH is exact (0), which adds verbosity.

XLOOKUP:
XLOOKUP doesn’t care about column order. You simply tell it what to look for and where to return the value from.

• Select a cell and insert the dynamic XLOOKUP formula.

=XLOOKUP(H12, B2:B23, A2:A23)

This formula looks for the Product Name and returns the corresponding Product ID (P006)—even though the ID is to the left. Cleaner syntax, no nesting required.

Practical Use:
Suppose your sales dashboard lets users type a product name in a search box. You can instantly retrieve the Product ID—or even multiple details (price, stock, supplier)—without rearranging columns:

=XLOOKUP(H12, B2:B23, A2:E23)

This returns multiple columns by “spilling” across adjacent cells, so you get all product info in a single step.

Reason 2: Built-In Error Handling (No More #N/A)

When a lookup value isn’t found, Excel returns #N/A. With VLOOKUP or INDEX/MATCH, you’d typically wrap the formula with IFERROR to handle this gracefully—adding extra nesting.

XLOOKUP has a built-in if_not_found argument, so you can specify a friendly message or fallback value right in the formula.

Let’s search for a product unit price that may not exist in the database (e.g., “Wireless Charger”).

VLOOKUP + IFERROR:

• Select a cell and insert the following formula.

=IFERROR(VLOOKUP(H4,D2:F23,1,FALSE), "Not Found")

This works, but it’s verbose and adds another layer of nesting.

INDEX/MATCH + IFERROR:

• Select a cell and insert the following formula.

=IFERROR(INDEX(D2:D23, MATCH(H8, B2:B23, 0)), "Not Found")

Again, it works, but the extra wrapper makes the formula longer.

XLOOKUP:
XLOOKUP’s if_not_found argument handles missing values elegantly.

• Select a cell and insert the following formula.

=XLOOKUP(H12, B2:B23, D2:D23, "Not Found")

This formula searches the Product Name; if it isn’t found, it returns “Not Found.” It’s concise and avoids extra functions.

Reason 3: Wildcard Searches Made Simple

VLOOKUP supports wildcards (* and ?) in exact-match mode (FALSE) for text lookups, but the syntax isn’t always obvious. INDEX/MATCH also supports wildcards because MATCH recognizes them when match_type is 0. XLOOKUP makes the intent explicit with a match_mode of 2 for wildcard matching, which improves clarity and reduces surprises.

Imagine you only remember part of the product name—maybe it starts with “Lap”—and you want to find its price.

VLOOKUP:

• Select a cell and insert the following formula.

=VLOOKUP("Lap*", B2:D23, 3, FALSE)

This can work with exact match using wildcards and does not require sorted data. However, the “column index” argument can be brittle if columns move.

INDEX/MATCH:

• Select a cell and insert the following formula.

=INDEX(D2:D23, MATCH("Lap*", B2:B23, 0))

This also supports wildcards via MATCH. It’s reliable, but the two-range setup is more verbose.

XLOOKUP:
XLOOKUP’s match_mode makes wildcard searches straightforward.

• Select a cell and insert the following formula.

=XLOOKUP("Lap*", B2:B23, D2:D23, "No match", 2)

• “Lap*”: partial match (matches any text starting with “Lap”)
• Lookup array: B2:B23
• Return array: D2:D23
• if_not_found: “No match”
• 2: enables wildcard search

This formula returns the product price (e.g., $30) using wildcard matching—perfect for partial name searches, SKU lookups, or anytime you don’t have the exact value.

Important Note: XLOOKUP is available in Microsoft 365, Excel 2021, and later versions. If you’re on an older version, you’ll need to stick with VLOOKUP or INDEX/MATCH for now.

Wildcard Tips:

Download Practice Workbook

Final Thoughts

XLOOKUP is not just another lookup function—it’s a rethinking of how lookups should work in Excel. In this tutorial, we showed three reasons why XLOOKUP is your new best friend. By combining directional flexibility, built-in error handling, and explicit wildcard matching, it saves time and prevents formula errors. Once you try it, you’ll never go back.