XLOOKUP vs VLOOKUP

Forget about the VLOOKUP function. The new XLOOKUP function has arrived, being a genuine alternative for VLOOKUP, HLOOKUP, INDEX and MATCH. If you know how to use the XLOOKUP function, you will never bother to use VLOOKUP anymore. The new feature, which has been recently introduced, has more options and is characterised by its flexibility, efficiency and user-friendliness.

XLOOKUP has six arguments:

=XLOOKUP(Lookup_Value,Lookup_Array,Return_Array,If_not_Found,Match_Mode,Search_Mode)​​

1. Lookup_Value
2. Lookup_Array
3. Return_Array
4. [If_not_Found] – #N/A
5. [Match_Mode] – default FALSE – Exact match
6. [Search_Mode] – default TRUE – First to last

Benefits of XLOOKUP over VLOOKUP:
✅ the searched result in the column may be placed to the left of the column with the searched value;

✅ change of the structure of the table with data by inserting or removing columns will not affect the result;

✅ we select only relevant ranges and not necessarily the entire table;

✅ it can return a range, not just a value;

✅ it can return a value, if not found;

✅ the exact search is set to 0 by default in argument 4, the most commonly used option;

✅ it can return the nearest higher value if we declare (1) in the 4th argument;

✅ should we look for the closest value in the 4th argument we do not need to have any data sorted;

✅ wildcard support only if you declare (2) in the 4th argument;

✅ it can search for the last value (-1) in argument 5;

✅ it carries out binary search;

✅ it performs searches not only vertically, but also horizontally if we mark the ranges accordingly;

✅ faster functionality.

You can learn similar issues and tricks at my comprehensive Excel Training in London.