The wait is over!! Finally, Microsoft has released an update to Office365 with a brand-new formula “XLOOKUP”.
XLOOKUP has the following advantages, compared to its predecessors.
- Can lookup on both left and right directions.
- Exact match is default.
- Can return FIRST or LAST match.
- XLOOKUP also replaces VLOOKUP & HLOOKUP.
Earlier, INDEX-MATCH formula combination was used to lookup values where HLOOKUP & VLOOKUP fell short. Now, XLOOKUP can replace all other functions. It is a robust and easy to use formula which can lookup values from right to left and vice versa.
Let us consider the below example to get deep insight of the formula. Our agenda is to fill column D of Table1 (Salary) using XLOOKUP
Lookup_value: Exactly same as VLOOKUP. In this scenario, let us consider Column B of Table1 as our lookup value.
Lookup_Array: Typically, the destination range/column where XLOOKUP will look for the match. Column H of Table2 would be our lookup array.
Return_Array: This argument is asking for the return value. In this case, we want salary column to be filled so we would consider Column G of Table2 as the return value.
[match_mode]: This is an optional argument in XLOOKUP formula. It can be any one of the following. By default, it is “Exact Match”.
- 0 – Exact match
- -1 – Exact match or next smaller item
- 1 – Exact match or next larger item
- 2 – Wildcard character match.
[Search_mode]: It can be either 1 or -1. “1” is to search from first to last and “-1” is from last to first.
Let us write the below formula in cell “D3” of table1 and the output looks as below.