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

- VLOOKUP (
- 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

- XLOOKUP(
- 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)

- match_mode

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)

XLOOKUP is available to Office Insiders only as of Aug 2019. It will become available to everyone in Oct 2019. Follow up out Twitter account for a tweet regarding its availability.