All courses available in-class and remotely.

What is xlookup?

What is XLOOKUP and how to use it

March 12, 2020
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:

1. XLOOKUP is more straight forward
- The value you are looking for
- The list where this value should be found
- The list from which you want the result
- [optional] value if not found

2. 4th parameter to support value not found

3. XLOOKUP offers additional parameters to VLOOKUP

4. It returns reference as output
(meaning you can combine XLOOKUP outputs in innovative ways with other formulas)

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 

Contact Us

What is Remote Training?

Valued by Individuals

4.72 / 5
Over 31085 Reviews

Trusted by Business

Procured by Government

Awards and Accreditations

Follow us