When Microsoft began talking about their new XLOOKUP function in the second half of last year, it was no wonder excel users were making a lot of noise about it. Designed to rectify some of its predecessors’ limitations, XLOOKUP has some pretty impressive features. For example, XLOOKUP can look to its left, can return multiple results and can work with vertical and horizontal data. Needless to say, XLOOKUP is a more versatile, intuitive and efficient upgrade from its older brother.

Let’s take a look at some of the key features of the XLOOKUP features:

2. 4th parameter to support value not found

3. XLOOKUP offers additional parameters to VLOOKUP

Let’s look at some working examples of how to use the XLOOKUP function:

Example 1

This example is from the video above, and uses a simple XLOOKUP to look up a country name, then return its telephone country code. It only includes the lookup_value (cell F2), lookup_array (range B2:B11), and return_array (range D2:D11) arguments. It does not include the match_mode argument, as XLOOKUP defaults to an exact match.


Note: XLOOKUP is different from VLOOKUP in that it uses separate lookup and return arrays, where VLOOKUP uses a single table array followed by a column index number. The equivalent VLOOKUP formula in this case would be: =VLOOKUP(F2,B2:D11,3,FALSE)


Example 2

In this example, we're looking up employee information based on an employee ID number. Unlike VLOOKUP, XLOOKUP is able to return an array with multiple items, which allows a single formula to return both employee name and department from cells C5:D14.

Try it yourself and let us know what you think. If XLOOKUP is not yet appearing in your function library you look our for it in your next update.

View our Excel Courses