Why XLOOKUP is the Modern Replacement for VLOOKUP

Introduced in 2019 to solve the limitations of older functions, XLOOKUP is more powerful, flexible, and easier to use. It works in any direction (left, right, up, down) and defaults to exact match — no more #N/A surprises from unsorted data.

Key Advantages of XLOOKUP

• No More Column Counting: You select the lookup array and the return array separately. Never count columns again.
• Horizontal and Vertical Lookups: XLOOKUP replaces both VLOOKUP and HLOOKUP with one unified function.
• Built-in Error Handling: Pass a custom message (e.g., "Not Found") as the fourth argument instead of wrapping everything in IFERROR.
• Reverse Search: Use search_mode = -1 to find the last match instead of the first.

Step-by-Step: Look Up an Employee's Department

Let's walk through a real example. Suppose you have employee IDs in column A and department names in column C. You want to find which department employee E-104 belongs to.

1. Pick the lookup value — the employee ID you're searching for: E-104 (or a cell reference like A2)
2. Set the lookup array — the column containing all employee IDs: A:A
3. Set the return array — the column with department names: C:C
4. Add a fallback (optional) — return "Not Found" if the ID doesn't exist

The complete formula: =XLOOKUP("E-104", A:A, C:C, "Not Found")

Unlike VLOOKUP, XLOOKUP works even if the department column is to the left of the employee ID column — no column reordering needed.

XLOOKUP vs VLOOKUP vs INDEX MATCH — Which Should You Use?

Handling Errors with XLOOKUP (if_not_found)

The if_not_found parameter is XLOOKUP's built-in safety net. Instead of getting an ugly #N/A error, you control what appears:

• Show custom text: =XLOOKUP(A2, B:B, C:C, "Not in database")
• Return a blank cell: =XLOOKUP(A2, B:B, C:C, "") — cleaner than IFERROR(XLOOKUP(...), "")
• Chain with other formulas: =XLOOKUP(A2, B:B, C:C, "Check ID " & A2 & " — not found")

This single feature eliminates the need for IFERROR wrappers in most lookup scenarios.

XLOOKUP with Multiple Criteria

Need to match on more than one condition? Use the & operator to build a composite key:

In this formula:

• A2 & B2 concatenates the two lookup values (e.g., "SalesEast")
• A:A & B:B builds the same composite key for every row in the lookup table
• XLOOKUP finds the row where both conditions match

Note: In older Excel versions you may need to press Ctrl+Shift+Enter for array formulas. Excel 365 and Google Sheets handle them natively.

Formula Syntax Reference

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