XLookup Function
By Richard Rost

6 years ago

How to use the XLookup function in Excel 365

S M L XL FS | Slo Reg Fast 2x | Bookmark

XLookup is a newly added function to Microsoft Excel in 2019 for Microsoft 365 subscribers and is a powerful replacement for VLookup and HLookup. In this example, I'll show you how to look up employee pay rates for your time sheets.

Clarification

In the video I mention that XLOOKUP is available for Microsoft Excel 2019. My apologies, it is ONLY available for Microsoft 365 subscribers. I believe I read somewhere at the time it was released that they would be adding it to Excel 2019, but apparently it's only for subscribers. Sorry for the confusion.

Members

There is no Extended Cut for this video.

Links

Excel Expert 11
VLookup Tutorial
Basic Functions Tutorial
Absolute References

Learn More!

Questions?

Please feel free to post your questions or comments below. Thanks.

Comments for XLookup Function

Age	Subject	From
6 years	When will I get XLOOKUP?	Mubeezi Micah
6 years	New XLOOKUP TechHelp Video	Richard Rost

Subscribe to XLookup Function
Get notifications when this page is updated

Intro

In this video, we will learn how to use the new XLOOKUP function in Microsoft Excel 2019 as a powerful replacement for VLOOKUP and HLOOKUP. I will show you how to look up values in a list, return results from multiple columns, perform searches in both directions, and take advantage of features like exact and approximate matching, wildcards, and error handling. We will walk through a real-world example using an employee timesheet to look up pay rates, and cover important tips like setting absolute references for autofill.

Transcript

Welcome to another TechHelp video brought to you by ExcelLearningZone.com. I am your instructor, Richard Rost.

Today we're going to learn about the XLOOKUP function, which is new in Excel 2019, and it's a powerful replacement for the VLOOKUP and HLOOKUP functions.

Normally, my TechHelp videos are based on an email that I might receive from a specific user. However, this one is different. I've literally gotten asked this by a dozen people in the last week or two. I've been hearing a lot about the new XLOOKUP function that Microsoft added to Excel 2019. Can you teach us how to use it?

Of course, I love XLOOKUP. It's a great new function. It's much more powerful than VLOOKUP, and VLOOKUP used to be one of my favorite functions. But now, XLOOKUP takes all the shortcomings and gets rid of them.

What is it? Well, XLOOKUP lets you look up a value in a list. So you can set up your own list of options, and then in your main spreadsheet, you can look up those values. For example, an employee ID will look up their name, and I'll show you how to use it in just a minute.

Now, if you're already familiar with VLOOKUP, these are the ways that XLOOKUP is better. You can look up to the left or the right. With VLOOKUP, you can only look up to the right. This is important because sometimes you have an existing list that you don't want to modify just so that it works with VLOOKUP.

You can return multiple results. It can bring back two, three, four columns, however much you want. VLOOKUP only worked with one column. That's important when we do double lookups. XLOOKUP replaces both VLOOKUP and HLOOKUP, so you can do horizontal or vertical lookups.

You can do reversed searches - look from last to first, instead of first to last. VLOOKUP starts with the beginning of the list and just goes down. XLOOKUP can go backwards.

XLOOKUP defaults to an exact match, so it'll find the exact value. There is also the option for the approximate match. XLOOKUP supports wildcards, which is great because you can search for part of a string.

You can specify a value if it's not found in the list, so you can put in "not found" or whatever instead of getting an error. You can return entire columns or rows, and XLOOKUP works with arrays.

So how does all this cool stuff work? Well, let's say you're doing timesheets and you've got a list of employees and how many hours they worked. You want to look up what their pay rate is so you can calculate how much to pay them.

Let's say you've got the date here, the employee, and the hours worked. Let's resize these just a little bit. I like to add a little splash of color, makes my spreadsheet a little happier.

So you've got 4-1, Joe worked four hours. 4-1, Sue worked eight hours. 4-1, Bill worked ten hours. Next day, 4-2, Joe worked six hours. 4-2, Sue was off and Bill worked twelve hours. Then 4-4, Sue came in and worked sixteen hours. You have your basic timesheet here.

I personally like to left-justify everything. That's just me. Over in this column, I want to look up their pay rate. Now, where am I going to get their pay rate from? Well, I'm going to make a little chart over here. It doesn't matter where - you can put it anywhere you want.

The employee and their pay rate. This would be a separate little, kind of like a little table in here. It's not really a table - I hesitate to use the word table because that's an actual thing, but it's a little mini sheet over here.

This could be on a different sheet, by the way. If you want to come down here and throw it on sheet two, that's fine. XLOOKUP works across multiple sheets, even across multiple workbooks. Today we're just going to keep it simple for class.

Put your list of employees: Joe, Sue, and Bill. That's the pay rate. Joe makes $12 an hour, Sue makes $15 an hour, Bill makes $18 an hour. Add a little formatting just to separate this as a different section. Maybe make these currencies.

Now, how do I get this over here? Well, that's where the XLOOKUP function comes into play. This is going to be =XLOOKUP(lookup_value, lookup_array, return_array). What value do you want to look up? I want to look up the employee's name Joe.

The lookup array: Where is the list of items that you're looking up? Well, I'm looking up Joe in this list right here. The return array: Here is the list of items you want to return - that's right here.

Then you can enter a value if not found, a match mode, search mode, all that stuff. We're just going to leave it like that. Those are optional - they're in brackets. We don't need them. Close the parenthesis and then press Enter.

Look at that. It brought back a 12. It took Joe, went over here, looked it up, and then brought back the return value, which is in the return array, which is 12, and put it right there. That's how XLOOKUP works.

Now, if you're really new to Excel and you've never used a function before like SUM or AVERAGE, I've got another tutorial that explains how that works and I'll put a link in the description below.

Now I want to autofill this to the rest of my list, but I have to make one more little change. Remember absolute and relative references. Again, I've got a lesson explaining that - look in the description below.

You have to change these values here to absolute references if you want to autofill this down. Click on it and hit F4 like that. F4, F4, F4. That changes these to absolute references. That means this box is never going to change. Otherwise, when I autofill this down, it's going to update this function. Just put those dollar signs in there like I show you right here. Press Enter.

Now I can autofill this down and I get all the correct values. Joe is 12, Joe is 12, Sue is 15, Bill is 18.

Now I can calculate the total pay, which is going to be =hours * pay rate. Enter. There we go. That is the beauty of XLOOKUP. Not that hard to set up. Took me just a couple minutes.

There's lots more I can do too. If you want to learn more about XLOOKUP, I spend about an hour covering it in my Excel Expert 11 class. I'll show you a bunch of different examples and how to use XLOOKUP. We'll do an example similar to what we just did where we have a weekday using the WEEKDAY function and then we'll pull back the actual name.

I'll show you a wildcard lookup so you can type in a portion of the name up here and it'll pull back their ID. We'll do student letter grades. We'll use what's called an approximate search where you type in something that's kind of in the list, like between two values, and it brings back the right information. For example, an 85 here isn't in the list, but XLOOKUP knows that that's a B.

Then we'll do a double lookup. For example, we have a list of clothing types and sizes and you want to bring back the price. You can come over here and type in jacket and then it brings back jacket small. It looks it up over here: jacket, small, $15. That's a double lookup or a two-way lookup.

Then I'll show you an approximate search with a double lookup, and we'll put a combo box on here so you can pick math from a list, type in the grade 68, and that student got a D. That's just some of the stuff covered in Excel Expert 11.

If you're new to Excel and need even more basic training, I have a lesson on functions available. There's a link; it teaches things like SUM. If you're not sure about those absolute references, those things with the dollar signs, I've got a lesson for that.

If you want to go old school or if you have an older version of Excel, older than 2019, you can use the VLOOKUP function which isn't quite as powerful as XLOOKUP but still gets the job done. There's a lesson on that as well. I'll put links to all these in the description below the video.

Thank you for watching. I hope you learned something. While you're here, be sure to subscribe to my channel and make sure you ring the bell. Click on the little bell and click "all" to get a notification by email every time I post a new lesson.

Make sure you visit my Excel forum. I've got a very active forum on my website where you can post questions and answers. If you need help, I've got a TechHelp page available where I can help you with your spreadsheets. You can always contact me directly by email. There's my email address.

Here's all the fun stuff - blog, Facebook, Twitter, YouTube, all that.

Here comes the shameless advertising portion of the lesson. Watch my free Excel Level 1 video if you're new to Excel. Right there's the link. When you're done with Level 1, if you want to purchase Level 2, it's just one dollar. Those are complete hour-plus long lessons.

Thanks again for watching. I hope you learned something and we'll see you again soon.

Quiz

Q1. What is the primary purpose of the XLOOKUP function in Excel?
A. To create complex charts and graphs
B. To look up values in lists or tables
C. To calculate mathematical equations
D. To sort data alphabetically

Q2. Which Excel versions first included the XLOOKUP function?
A. Excel 2013
B. Excel 2016
C. Excel 2019 and newer
D. Excel 2010

Q3. What major limitation of VLOOKUP does XLOOKUP overcome?
A. Only searches horizontally
B. Only looks up values to the right
C. Cannot sort data
D. Only works with numbers

Q4. With XLOOKUP, can you look up values to the left of your lookup column?
A. No, only to the right
B. Yes, both left and right
C. Only upwards in a table
D. Only in the same column

Q5. What is one advantage of XLOOKUP over VLOOKUP regarding result columns?
A. Can return multiple columns of results
B. Requires all data in one column
C. Only returns numeric values
D. Must use only the first row

Q6. Which of the following features does XLOOKUP NOT support?
A. Wildcard searches
B. Specifying a custom value if not found
C. Returning entire arrays
D. Creating pivot tables

Q7. If XLOOKUP does not find a match, what can you specify to return instead of an error?
A. 'Not found' or any custom message
B. Zero only
C. Always returns an error
D. The first value in the list

Q8. Which statement about XLOOKUP's match type is TRUE?
A. Approximate match is the default
B. Exact match is the default
C. You cannot set the match type
D. Match type does not affect results

Q9. How does using absolute references ($) in cell references help when copying and autofilling XLOOKUP formulas?
A. Makes the spreadsheet colorful
B. Lasers the data into a table
C. Prevents the lookup range from moving when autofilling
D. Changes the values to percentages

Q10. XLOOKUP can handle lookups in which situations?
A. Only within a single sheet
B. Within and across multiple sheets and even workbooks
C. Only in adjacent columns
D. Only in empty rows

Q11. Which function did XLOOKUP replace by combining their functionalities?
A. SUM and COUNT
B. SUM and HLOOKUP
C. VLOOKUP and HLOOKUP
D. COUNT and AVERAGE

Q12. Which of the following is a correct basic syntax for XLOOKUP?
A. =XLOOKUP(lookup_value, range)
B. =XLOOKUP(value, array, array)
C. =XLOOKUP(lookup_value, lookup_array, return_array)
D. =XLOOKUP(value1, value2, value3, value4)

Q13. What happens if you do not make the lookup and return arrays absolute references before autofilling?
A. The function stops working completely
B. The references may change incorrectly and yield errors
C. The cells will become locked
D. The formula will delete data

Q14. Which of the following is NOT presented in this TechHelp video as a feature or example of XLOOKUP?
A. Double lookup or two-way lookup
B. Returning letter grades based on a numerical score
C. Performing approximate matches
D. Creating dynamic dashboards

Q15. Where can you find more Excel help and community support from the instructor?
A. ExcelLearningZone.com forum
B. Only on YouTube
C. On the local network
D. Only in printed manuals

Answers: 1-B; 2-C; 3-B; 4-B; 5-A; 6-D; 7-A; 8-B; 9-C; 10-B; 11-C; 12-C; 13-B; 14-D; 15-A

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Excel Learning Zone focuses on the XLOOKUP function, which was introduced in Excel 2019. I am your instructor, Richard Rost.

In recent weeks, there has been a lot of interest in XLOOKUP, with numerous people reaching out to learn more about how it works. Microsoft added this new function to Excel 2019, and it has become a powerful replacement for both VLOOKUP and HLOOKUP. Personally, I enjoy using XLOOKUP because it addresses many of the limitations that existed with VLOOKUP.

To understand what XLOOKUP does, consider a situation where you want to search for a value in a list and retrieve related information. For instance, if you have a list of employee IDs, you might want to look up an employee's name based on their ID. XLOOKUP handles tasks like this with far more flexibility than its predecessors.

Compared to VLOOKUP, XLOOKUP offers several key advantages. First, it allows you to search to the left or the right within your data, whereas VLOOKUP is limited to searching only to the right. This gives you more freedom to organize your data without having to rearrange your lists just to make a lookup function work. Additionally, XLOOKUP can return multiple columns of results at once, not just a single value. This is particularly useful for more complex lookups. It also replaces HLOOKUP, so you do not have to worry about whether your data is structured horizontally or vertically.

Another helpful feature is the option to perform reverse searches, so you can start searching from the bottom of the list upwards, unlike VLOOKUP, which always goes from top to bottom. XLOOKUP defaults to an exact match, but you can also set it for approximate matches. It supports wildcards, making it easier to find parts of strings. Moreover, you can specify a custom message or value if no match is found, instead of just seeing an error. XLOOKUP can return entire columns or rows and is compatible with arrays, making it very versatile.

Let me walk you through a practical example. Imagine you are working with timesheets and need to calculate employee pay. You have a list that includes the date, employee name, and hours worked. To the side, set up a simple chart with employee names and their corresponding pay rates. This chart can exist anywhere on your spreadsheet, or even on a different sheet or workbook. For this example, let's keep everything on one sheet for simplicity.

You would list your employees - Joe, Sue, and Bill - along with their respective hourly rates, such as $12, $15, and $18 per hour. To distinguish this chart, you might add some formatting and set the values as currency.

The next step is to retrieve the pay rate for each employee in your timesheet. This is where the XLOOKUP function becomes particularly useful. You will set it up so that it looks up the employee's name in your pay rate chart and retrieves the corresponding pay rate. The function uses three main arguments: the lookup value (the employee name), the lookup array (where you are searching for the name), and the return array (the matching pay rates).

There are also optional arguments for situations such as when a value is not found or when using different match or search modes, but for basic use you can omit these. After entering the function, it brings back the correct pay rate for your employee. For example, when you look up "Joe," you should see the value 12.

If you are new to using functions such as SUM or AVERAGE in Excel, I have a separate lesson that covers those fundamentals. You can find a link for that on my website.

When you want to autofill the XLOOKUP formula for all your employees, be mindful of absolute and relative references. If you want the formula to work correctly as you copy it down your sheet, convert your lookup arrays to absolute references by adding dollar signs, for example, by pressing F4. This ensures the reference does not shift as you drag the formula down. After this, you can autofill and each row will pull back the correct pay rate.

Once you have the pay rates in place, you can calculate total pay by multiplying the hours worked by the pay rate for each entry. This demonstrates how straightforward and efficient XLOOKUP makes these kinds of lookups in Excel.

There are even more capabilities within XLOOKUP. In my Excel Expert 11 course, I spend about an hour exploring different ways to use this function. I cover more advanced examples, including looking up day names using WEEKDAY, partial name searches using wildcards, assigning student letter grades with approximate matching, performing double lookups for things like garment sizes and prices, and even combining these features with form controls like combo boxes.

If you need a refresher on the basics of Excel functions or want to learn more about working with absolute references (the dollar signs in your formulas), I have additional tutorials available. For those still using older versions of Excel, I also provide instruction on VLOOKUP, which remains a useful function even though it is not as powerful as XLOOKUP.

For more learning, be sure to subscribe to my channel and enable notifications so you never miss new lessons. You are also welcome to visit my Excel forum to ask or answer questions and to reach out to me directly by email if you need personal help.

Finally, if you are just getting started with Excel, watch my free Excel Level 1 video, and consider purchasing Level 2 for a more in-depth experience.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Introduction to the XLOOKUP function in Excel 2019
Comparing XLOOKUP with VLOOKUP and HLOOKUP
Capabilities of XLOOKUP: lookup left or right
Returning multiple columns with XLOOKUP
Reversed search with XLOOKUP (last to first)
Exact match and approximate match options in XLOOKUP
Using wildcards in XLOOKUP searches
Specifying a value if no match is found in XLOOKUP
XLOOKUP across multiple sheets
Constructing a sample timesheet for practical demonstration
Creating and formatting a lookup table for employee pay rates
Writing a basic XLOOKUP formula
Setting up absolute references for autofill in XLOOKUP
Autofilling XLOOKUP formulas down a column
Calculating total pay using hours and pay rate with XLOOKUP