XLOOKUP Generator - Modern VLOOKUP Replacement | Free

Wizard Mode

Searches a range or an array, and then returns the item corresponding to the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match.

Configuration

Visual Helper

#
A
B
C
D
1
Item 1
$100
2
ID-123
Item 2
$200
3
Item 3
$300
4
Item 4
$400

Result Function

=XLOOKUP(lookup_value, lookup_array, return_array)

Why XLOOKUP is the Modern Replacement for VLOOKUP

Introduced 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 an exact match.

Key Advantages of XLOOKUP

  • No More Column Counting: You select the lookup array and the return array separately.
  • Horizontal Lookups: XLOOKUP replaces both VLOOKUP and HLOOKUP.
  • Built-in Error Handling: You can define what to display (e.g., "Not Found") directly within the formula if a match isn't found.

Formula Syntax Example

=XLOOKUP(search_value, lookup_array, return_array, [if_not_found])

Common Errors & Fixes

  • XLOOKUP returns #N/A

    Causes:
    • Lookup value not in lookup_array.
    • Lookup and return arrays different lengths.
    • Data type or format mismatch.
    Fixes:
    • Use fourth argument (if_not_found) to return a default.
    • Ensure lookup_array and return_array have the same number of rows.
    • Use TRIM, VALUE, or TEXT to align formats.

Related Formulas

VLOOKUPINDEX/MATCHIFERRORSUMIF

Want to become an Excel Pro?

Stop searching for formulas. Master Excel in 30 days with this top-rated course.

Learn More