# XLOOKUP: A Brand-new formula in Office365

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.

Syntax:

=XLOOKUP(lookup_value,lookup_array,return_array,[match_mode],[search_mode])

Explanation

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.

XLOOKUP(\$B\$3:\$B\$9,\$H\$3:\$H\$9,\$G\$3:\$G\$9,0,1) 