All courses available in-class or via remote login

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 

What is Remote Training?

Valued by Individuals

 

4.8/5
Over 21505 Reviews
So much was covered and John stayed back to ensure we did not miss any of the curriculum, fantastic -Office365 Beginner Melbourne
Julie - Office365 Beginner, .
I was the only student so everything was tailored around me which was fantastic -Office365 Intermediate Perth
Julie - Office365 Intermediate, .
Overall training was good -Power BI Beginner Perth
Archie - Power BI Beginner, .
Great intro to Power BI -Power BI Beginner Perth
Ara - Power BI Beginner, .
Thanks David. Good start into the Power BI World. -Power BI Beginner Perth
Juanita - Power BI Beginner, .
Great course, I have learned a lot that I can implement in my workplace. Thanks Steven -Office365 Intermediate Brisbane
Jules - Office365 Intermediate, .
Great, while I knew most of it I obtained some golden ideas. See you in a month for the advanced course. -Office365 Intermediate Brisbane
David - Office365 Intermediate, .
Very useful thank you. Jagg has been very nice and helpful. -Excel Intermediate Adelaide
Cassia - Excel Intermediate, .
Great course. Learnt so much -Excel Intermediate Adelaide
Kacey - Excel Intermediate, .
It was great! Very great at explaining concepts. -Excel Intermediate Adelaide
James - Excel Intermediate, .
Course was very informational, however, I probably should have done the advanced class - it was a lot of stuff I already knew with a few hidden advantages. Course trainer Jagg is very knowledgeable and hosted a professional training session, -Excel Intermediate Adelaide
Chantelle - Excel Intermediate, .
clear and useful information, Jagg kept the course interesting and was very helpful. thank you. -Excel Intermediate Adelaide
Vanessa - Excel Intermediate, .
Course will be very useful for new role at work. -Power BI Beginner Melbourne
Alex - Power BI Beginner, .
Thank you -Power BI Beginner Melbourne
Amonrat - Power BI Beginner, .
Well done. Now I will practice -Power BI Beginner Melbourne
Jamie - Power BI Beginner, .
It was interesting. Learnt a few new things -Power BI Beginner Melbourne
Purva - Power BI Beginner, .
Great session, very simple to use and easy to follow along. Thanks for a great session. -Power BI Beginner Melbourne
Lindsey - Power BI Beginner, .
great beginner course! -Power BI Beginner Melbourne
Sarah - Power BI Beginner, .
John was really good at explaining and answering queries. Few technical difficulties with some students who is was able to sort out there problems. -Power BI Beginner Melbourne
Aaron - Power BI Beginner, .
Good session, very informative and took a lot from it -Power BI Intermediate Sydney
Nathan - Power BI Intermediate, .

Trusted by Business

Procured by Government

Map of Australia