XLOOKUP function – XLOOKUP is a new and exciting function but doesn’t exist in Office 2019. XLOOKUP function can install in Office 2007 to 2019. The XLOOKUP function is an improved form of HLOOKP, VLOOKUP, MATCH, INDEX, and IFERROR function which are used to return values.

 XLOOKUP Function in Excel

Step 1: Open Microsoft Excel 2007 to 2019 any version

Step 2: Click on File -> Options

Step 3: In Excel Option Click on Add-ins

Step 4: Click on the GO button, with Excel Add-ins

Step 5: In Add-ins, Click on Browse button, and select the file based on 32Bit, or 64Bit Office version (How to check Office version)

Step 6: Click on OK button, now you use XLOOKUP function in Excel

Download From Microsoft

Download [ XLOOKUP Function ]

Here are some XLOOKUP function benefits over VLOOKUP:

1. XLOOKUP function can perform with vertical and horizontal data.
2. XLOOKUP can also search in reverse order (While VLOOKUP has the limitation of searching the values in reverse order independently).
3. XLOOKUP can return results in more than one column.

Syntax of XLOOKUP Function: –

 XLOOKUP (LOOKUP, LOOKUP_ARRAY, RETURN_ARRAY, [NOT FOUND], [MATCH MODE], [SEARCH MODE])

Detail of Parameters

XLOOKUP Basic parameters

• LOOKUP: the value you are looking for
• LOOKUP_ARRAY: the range to search for the lookup value
• RETURN_ARRAY: the range that contains the return values

XLOOKUP optional parameters

• NOT FOUND: when a valid search is not found, return the text you supply. This section is alternate function of IFERROR
• MATCH MODE: This section is further divided into four sections, which are explained below.
  • 0 means that XLOOKUP function will look for only exact match.
  • -1 means if there is no exact match return next smaller item.
  • 1 means if there is no exact match return next larger item.
  • 2 – the use of wildcard (*, ?) match

• SEARCH MODE: This section is also further divided into four sections, which are explained below.
  • 1 mean to perform the search starting at the first item. This is the default option.
  • -1 mean to perform a reverse search starting at the last item.
  • 2 mean to perform a binary search that relies on the lookup array being sorted in ascending order. If not sorted, invalid results will be returned.
  • -2 mean to perform a binary search that relied on the lookup array being sorted in descending order. If not sorted, invalid results will be returned.