How to Use XLOOKUP in Excel Like a Pro

Tired of wrestling with VLOOKUP’s limitations in Excel? Say hello to XLOOKUP—the modern, flexible function that makes your lookups easier, faster, and more powerful. Whether you’re pulling prices, matching IDs, or auto-filling reports, XLOOKUP can do it all.

In this guide, you’ll learn exactly how to use XLOOKUP, from the basics to advanced tips that save you time and frustration. No more index-match gymnastics or worrying about column positions. By the end, you’ll be confident using XLOOKUP in real-world Excel tasks.

Step-by-Step Guide to Using XLOOKUP

1. Understand the XLOOKUP Syntax

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

lookup_value: The value you want to search for.
lookup_array: The range where Excel looks for that value.
return_array: The range to return a matching value from.
Optional: if_not_found, match_mode, and search_mode allow more control.

2. Start with a Simple Lookup

Example: Find the department of employee ID 102.

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

Excel will search for 102 in column A and return the corresponding value from column B.

3. Handle Missing Values Gracefully

Add an error message if a match isn’t found:

=XLOOKUP(999, A2:A10, B2:B10, "Not found")

This prevents #N/A errors and makes your sheet cleaner.

4. Match Exactly or Approximately

Use the optional match_mode:
- 0 = exact match (default)
- -1 = exact or next smaller
- 1 = exact or next larger

=XLOOKUP(85, A2:A10, B2:B10, "Not found", -1)

5. Search from First or Last

Use search_mode:
- 1 = search from first (default)
- -1 = search from last

=XLOOKUP("ProductX", A2:A10, B2:B10, "Not found", 0, -1)

6. Replace VLOOKUP with XLOOKUP

Old VLOOKUP:

=VLOOKUP(102, A2:C10, 2, FALSE)

Better with XLOOKUP:

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

No need to count columns or worry about order.

7. Lookup from Right to Left

XLOOKUP works in any direction:

=XLOOKUP("Marketing", C2:C10, A2:A10)

Unlike VLOOKUP, which only searches left to right.

8. Nested XLOOKUP for Multi-Level Search

=XLOOKUP("HR", C2:C10, XLOOKUP(102, A2:A10, B2:B10))

Useful when your return array needs a second lookup.

Pro Tips & Workflow Improvements

Use Named Ranges: Improves readability and reusability.
Combine with Data Validation: Make dynamic dropdowns.
Pair with FILTER or SORT: Build interactive dashboards.
Use XLOOKUP in Tables: Structured references make formulas cleaner.
Shortcut: Type =XL and press Tab to autocomplete faster.

Advanced Use Case: Multi-Criteria Lookup

XLOOKUP can’t handle multiple criteria directly, but you can combine fields:

=XLOOKUP(A2&B2, D2:D100&E2:E100, F2:F100)

Concatenate lookup fields and lookup arrays for dual-condition lookups.

Troubleshooting & Common Mistakes

#N/A Error: Use the 4th argument to catch missing values.
Mismatched Ranges: Make sure lookup_array and return_array are the same size.
Incorrect Match Mode: If you’re not getting results, double-check exact vs. approximate.
Array Formula Confusion: XLOOKUP isn’t an array formula, but behaves like one—don’t wrap it unnecessarily.
Old Excel Version: XLOOKUP is available in Excel 365 and Excel 2019+. Older versions won’t support it.

Conclusion

XLOOKUP is a game-changer for Excel users. With its flexibility, clarity, and power, it replaces clunky old methods and speeds up your data tasks. Now that you know how to use it like a pro, go try it in your own sheets.

Next, you might want to explore FILTER or LET—two more powerful functions that pair beautifully with XLOOKUP.