What is xlookup in Excel

XLOOKUP is the newest addition to the lookup formulas in Excel. If VLOOKUP is a Vertical lookup function and HLOOKUP is a Horizontal lookup function, XLOOKUP is capable of replacing them both by performing the lookup both horizontally and vertically.

What is the difference between VLOOKUP and XLOOKUP?

VLOOKUP is structured like this

    • value to look for,
    • in this big array where the value has to be in the first column,
    • return the value in the X column on the right after the first column,
    • exact or approximate match)

Notice in bold the restrictions of VLOOKUP.

  • you have to define a whole array, a big square area made of many columns and rows
  • your value that you are looking for has to be in the first column always
  • the value you want in return has to be on the right side
  • you have to count the number of columns from the first one to the one containing the result (so easy to make mistakes in a large spreadsheet)
  • it defaults to an approximate match unless you make the last parameter FALSE. FALSE actually means exact match (what?)
  • you can only search from top to bottom. if you want to search from the bottom to the top, you have to re-sort your data.
  • you can’t insert or delete columns in the big array without modifying the formula (the count is wrong)

Why is XLOOKUP better than VLOOKUP

XLOOKUP is structured like this

    • value to look for
    • anywhere in this array
    • return the value from this other array)
  • there are a couple of optional parameters
    • match_mode
      • 0 – exact match – default (thank you)
      • -1 or 1 – exact match to smaller or larger item (when no exact match is found)
      • 2 – wildcard match
    • search_mode
      • 1 for top to bottom, -1 for bottom to top
      • 2 and -2 binary search (ascending or descending)

Ignoring the optional parameters that are not needed by most of the users, the XLOOKUP becomes a much better version to lookup a value.

Advantages of XLOOKUP

  • XLOOKUP can search on the left or on the right of the array containing the lookup value
  • XLOOKUP can have columns deleted or added between the two arrays with no effect on the formula
  • XLOOKUP searches for an exact match by default
  • XLOOKUP is easier to write (only 3 parameters instead of 4)