Excel XLOOKUP: Syntax and Examples

Key Insights

XLOOKUP replaces VLOOKUP and HLOOKUP with a simpler syntax that searches in any direction, returns multiple columns, and defaults to exact matches without manual configuration.
The function’s optional parameters enable powerful features like custom error messages, wildcard matching, approximate matches for ranges, and reverse search direction—all without nested helper functions.
While XLOOKUP requires Microsoft 365 or Excel 2021+, its cleaner syntax and built-in error handling make it the superior choice for modern workbooks over legacy INDEX-MATCH combinations.

Introduction to XLOOKUP

Microsoft introduced XLOOKUP in 2019 as the long-awaited successor to VLOOKUP and HLOOKUP. After decades of Excel users wrestling with VLOOKUP’s limitations—column index numbers, left-to-right restrictions, and cryptic #N/A errors—XLOOKUP delivers a modern solution that actually works the way you’d expect.

The function eliminates the need for most INDEX-MATCH combinations while providing capabilities that were previously impossible or required complex nested formulas. XLOOKUP searches bidirectionally, defaults to exact matches, returns entire rows or columns, and includes built-in error handling. If you’re still using VLOOKUP in 2024, you’re working harder than necessary.

Basic XLOOKUP Syntax

The XLOOKUP function follows this structure:

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

Required arguments:

lookup_value: The value you’re searching for
lookup_array: The range where you’re searching
return_array: The range containing values to return

Optional arguments:

[if_not_found]: Custom text to display when no match exists (default: #N/A error)
[match_mode]: How to match values (default: 0 for exact match)
[search_mode]: Search direction (default: 1 for first-to-last)

Here’s a simple product price lookup:

=XLOOKUP(D2, A2:A10, B2:B10)

This searches for the value in D2 within the range A2:A10 and returns the corresponding value from B2:B10. Unlike VLOOKUP, you don’t need to count columns or ensure your lookup column is leftmost.

Common XLOOKUP Use Cases

Exact Match Lookups

The most frequent use case is finding exact matches. XLOOKUP handles this by default:

=XLOOKUP(E2, A2:A50, C2:C50)

This formula searches for employee ID in E2 and returns their salary from column C. No need to specify exact match mode—it’s automatic.

Approximate Match for Ranges

Grade assignment based on score ranges demonstrates XLOOKUP’s approximate match capability:

=XLOOKUP(B2, {0,60,70,80,90}, {"F","D","C","B","A"}, , 1)

The 1 in the match_mode parameter finds the largest value less than or equal to the lookup value. A score of 85 returns “B” because 85 falls between 80 and 90.

For a more practical implementation with cell references:

=XLOOKUP(B2, $F$2:$F$6, $G$2:$G$6, "Invalid Score", 1)

Where F2:F6 contains thresholds (0, 60, 70, 80, 90) and G2:G6 contains grades.

Returning Multiple Columns

XLOOKUP’s ability to return entire rows eliminates the need for multiple lookup formulas:

=XLOOKUP(A15, A2:A10, B2:D10)

This single formula returns name, department, and salary simultaneously. The return_array spans three columns, so XLOOKUP spills the results across three cells. This is a game-changer for dashboard reporting where you need to display multiple related fields.

Left-to-Right Lookups

VLOOKUP’s infamous limitation was requiring the lookup column to be left of the return column. XLOOKUP doesn’t care:

=XLOOKUP(F2, D2:D50, A2:A50)

This searches in column D and returns values from column A—impossible with VLOOKUP without restructuring your data.

Advanced XLOOKUP Features

Match Mode Options

The match_mode parameter offers four options:

0: Exact match (default)
-1: Exact match or next smallest item
1: Exact match or next largest item
2: Wildcard match using *, ?, and ~

Wildcard matching enables partial text searches:

=XLOOKUP("*Smith*", A2:A100, B2:B100, "Not Found", 2)

This finds any cell containing “Smith” anywhere in the text—useful for searching names, product descriptions, or codes where you only know part of the value.

Search Mode Options

The search_mode parameter controls search direction:

1: Search first-to-last (default)
-1: Search last-to-first
2: Binary search ascending (requires sorted data)
-1: Binary search descending (requires sorted data)

Last-to-first search finds the most recent entry:

=XLOOKUP(A15, A2:A100, B2:B100, , 0, -1)

If your data contains duplicate lookup values and you need the last occurrence (like the most recent transaction for a customer), search_mode -1 solves this elegantly.

Nested XLOOKUP for Two-Way Lookups

Combining two XLOOKUPs creates powerful matrix lookups:

=XLOOKUP(G2, A2:A10, XLOOKUP(G3, B1:F1, B2:F10))

This performs a two-dimensional lookup—finding a row based on G2 and a column based on G3. Think of it as looking up a price in a table where rows represent products and columns represent regions.

Error Handling and If_Not_Found Parameter

The if_not_found parameter is XLOOKUP’s built-in error handling:

=XLOOKUP(D2, A2:A100, B2:B100, "Product not found")

Compare this to the VLOOKUP equivalent requiring IFERROR:

=IFERROR(VLOOKUP(D2, A2:B100, 2, FALSE), "Product not found")

XLOOKUP’s approach is cleaner and more intuitive. You can return text, numbers, or even formulas in the if_not_found parameter:

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

This concatenates the lookup value into the error message, providing more helpful feedback.

XLOOKUP vs VLOOKUP/INDEX-MATCH

Here’s the same employee salary lookup written three ways:

VLOOKUP:

=IFERROR(VLOOKUP(E2, A2:C50, 3, FALSE), "Employee not found")

INDEX-MATCH:

=IFERROR(INDEX(C2:C50, MATCH(E2, A2:A50, 0)), "Employee not found")

XLOOKUP:

=XLOOKUP(E2, A2:A50, C2:C50, "Employee not found")

The XLOOKUP version is immediately more readable. You don’t need to count columns or nest functions for basic error handling.

Performance considerations: For datasets under 10,000 rows, performance differences are negligible. XLOOKUP and INDEX-MATCH perform similarly, both outperforming VLOOKUP on large datasets. However, XLOOKUP’s binary search mode (search_mode 2 or -2) can significantly improve performance on sorted data exceeding 50,000 rows.

When to use each:

Use XLOOKUP for all new workbooks if you have Microsoft 365 or Excel 2021+
Use INDEX-MATCH if backward compatibility with older Excel versions is required
Avoid VLOOKUP unless maintaining legacy workbooks where changing formulas risks breaking dependencies

Common Pitfalls and Best Practices

Mismatched array sizes are the most common error. The lookup_array and return_array must have the same number of rows (or columns for horizontal lookups):

=XLOOKUP(A2, B2:B10, C2:C15)  // Error: arrays don't match
=XLOOKUP(A2, B2:B10, C2:C10)  // Correct: both have 9 rows

Data type mismatches cause silent failures. If you’re looking up “100” (text) in a column of numbers, XLOOKUP returns your if_not_found value. Use VALUE() or TEXT() to convert types when necessary:

=XLOOKUP(VALUE(A2), B2:B100, C2:C100)

Version compatibility is critical. XLOOKUP only works in Microsoft 365, Excel 2021, and Excel for the web. If your workbook might open in Excel 2019 or earlier, stick with INDEX-MATCH. There’s no graceful degradation—the formula simply breaks.

Performance with large datasets requires consideration. While XLOOKUP handles most scenarios efficiently, returning multiple columns across 100,000+ rows can cause calculation delays. In these cases, consider Power Query for data transformation or evaluate whether you need all columns in real-time.

Absolute vs relative references matter when copying formulas. Lock your lookup and return arrays with dollar signs:

=XLOOKUP(A2, $D$2:$D$100, $E$2:$E$100)

This allows you to copy the formula down while maintaining the correct reference ranges.

XLOOKUP represents a significant improvement in Excel’s lookup capabilities. Its intuitive syntax, built-in error handling, and bidirectional search make it the obvious choice for modern Excel work. The learning curve is minimal—if you understand VLOOKUP, you’ll master XLOOKUP in minutes. Start using it today, and you’ll wonder how you tolerated VLOOKUP’s limitations for so long.