Excel Xlookup Function: Overview With Examples

Now Office 365 comes with modern and flexible replacement of older functions like VLOOKUP, HLOOKUP, and LOOKUP that is XLOOPUP function. Excel 2019 and other previous versions won’t ever get this new function.

Here we are going to learn all about function, syntax, operational parameters with examples.

You are familiar with VLOOPUP, HLOOKUP, INDEX+MATCH, LOOKUP, etc. Now you are introduced to the XLOOKUP function. It allows users to search for an item in a range and return matching results. In most of the way, it is similar to VLOOKUP but offers more functionality than that, (XLOOKUP vs VLOOKUP) .

XLOOKUP function allows you to find values in a dataset (vertical or horizontal) and return the matching value in some other row/column. It means it supports approximate and exact matching, wildcards (*?) for partial matches and lookups in vertical or horizontal ranges.
For example, if you have an exam scoreboard of students, you can use XLOOKUP to quickly check how much a student has scored by using the name of the student.

If you go deep in the discussion, then here, you will find the power of this function through more examples.

How to Access XLOOKUP Function in Excel

As, this function is only available with the Office 365 (Home, Personal, or University edition).

First you need to go to File tab and then click on Account. There you find an Office Insider program, click it, and join the Office Insider Program. This way you will get access to the XLOOKUP function.

XLOOKUP Benefits

• It can lookup data to the right or left of lookup values.


• It can return multiple results.


• It can handle missing values with the inbuilt IFNA.


• It can work with vertical and horizontal data.


• Without sorting the table, it can find next smaller or next larger match.


• You can turn on wildcard.


• It looks for the exact match unlike VLOOKUP (defaults to approximate).


• It can perform reverse search means last to first.


• Not just one value but it can return entire rows or columns.


• To apply complex criteria, it can work with arrays natively.
  

XLOOKUP Function Syntax

XLOOKUP has three basic and three optional parameters total 6

1. lookup_value – That is the value we are looking up.

2. lookup_array - That is array in which you are looking for the lookup value.

3. Return_array - The range to return the value.

These are required arguments, below are optional one.

1. [if_not_found] - It will return the specified value when the lookup value is not found.

2. [match_mode] - This is the match to return. Here you can specify the type of match you need.

0 = This is the default match mode. Here XLOOKUP will look for the exact match. If it is not found, then the return value will the no applicable error.

-1. = An exact match is returned, but if when it is not found then the next smallest value is returned.

1. = An exact match is returned but if it is not found then the largest value is returned.

2= An exact match is returned but if special characters are used, it acts as wildcard characters in lookup_value and return partial matches.

3. [search_mode] – This is the type of search to perform by XLOOKUP.

1 – this is the default option. Search performed from first to last item in the lookup_array.

-1 - here the search performed from last to first item in the lookup_array.

2. – perform a binary search from first to last item in the lookup_array being sorted in ascending order. If not, then an invalid result will be returned.

-2 - perform a binary search from last to first item in the lookup_array being sorted in descending order. If not, then an invalid result will be returned.