Learn how to use Excel’s XLOOKUP function effectively. XLOOKUP is the modern replacement for VLOOKUP and HLOOKUP, offering more flexibility and power in data lookup operations.

Syntax

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

• lookup_value: The value you want to find (text, numbers, or cell reference)
• lookup_array: Where to look for the value (single column or row)
• return_array: What to return when value is found (can be multiple columns/rows)
• if_not_found: (Optional) What to show if no match (“Not Found”, 0, custom text)
• match_mode: (Optional) How to match:
  • 0: Exact match (default)
  • -1: Exact match or next smaller item
  • 1: Exact match or next larger item
  • 2: Use wildcards (* and ?)
• search_mode: (Optional) Search direction:
  • 1: First to last (default)
  • -1: Last to first
  • 2: Binary search (ascending)
  • -2: Binary search (descending)

Examples

1. Basic lookup (find employee salary by ID):
   =XLOOKUP(A2, EmployeeIDs, Salaries)

2. Multiple column return (get name and department):
   =XLOOKUP(A2, EmployeeIDs, NameDeptColumns)

3. With error handling (custom message):
   =XLOOKUP(A2, ProductIDs, Prices, “Product not found”)

4. Approximate match (find next larger price):
   =XLOOKUP(100, PriceList, ProductNames, “Not available”, 1)

5. Wildcard search (find items starting with “App”):
   =XLOOKUP(“App*”, ProductNames, Prices, “No match”, 2)

6. Reverse search (find last occurrence):
   =XLOOKUP(SearchValue, DataRange, ResultRange, “Not found”, 0, -1)

Advanced Tips

• Use multiple return columns: Select multiple columns in return_array
• Handle errors: Always include if_not_found parameter for better user experience
• Optimize large data: Use binary search (search_mode=2) for sorted lists
• Two-way lookup: Can search both vertically and horizontally
• Array formulas: Works with array returns for advanced calculations
• Nested XLOOKUP: Can be nested for complex lookups

Common Errors

• #N/A: Value not found (use if_not_found to handle)
• #VALUE!: Arguments have wrong data types
• #REF!: Invalid references in formula
• #SPILL!: Return array blocked by existing data