This class focuses on the new XLOOKUP function which was added to Excel in 2019 for Microsoft 365 subscribers. We will learn all of the ways to use XLOOKUP in addition to double-lookups (two-way lookups). We will also take some time to go over some string manipulation functions, turn on the Developer toolbar, and learn how to create Combo Boxes.

Please note: XLOOKUP is only available to Microsoft 365 subscribers, NOT retail Office 2019 users.

Video Time Conversion Find, Left, Len, Mid, Right XLOOKUP Function Two-Way (Double) Lookups Developer Toolbar Combo Box Control

If you would like a preview of what's covered in this class, click on the video player image below to watch the first lesson of this course free of charge, or scroll down for the complete course outline.

This course is for the expert user who has good experience with Microsoft Excel or has completed our Beginner Series and the previous Expert Series classes. This course primarily focuses on the XLOOKUP function which is new in Excel in 2019 for Microsoft 365 Subscribers ONLY. If you have an older version of Excel, you will only benefit from Lesson 1 (Video Time Conversion).

Lesson 1 covers mostly string manipulation. We will take a list of videos with their running times, and then pull that apart and change that so it reads time indexes to jump to if you put all of the videos together into one long video. Sound confusing? 

Basically what happened was... I was preparing a couple of my courses to put them up on YouTube. In so doing I decided to put together all of the individual smaller videos into one large video. So that the user can simply jump to any spot in the video, YouTube requires you to put the time index. I have the time indexes, it's just that I have to calculate it based on the running time of the previous videos. That's where this lesson came into being. I used a lot of cool text manipulation and math functions and it's just a great display of how you can put together a sheet with a lot of the techniques that we've already covered in previous classes. I just want to paste the list of videos with their times and have Excel convert that into time indexes for a single larger video. This is a really cool example.

Lesson 2 begins our coverage of the new XLOOKUP function. It's new in Excel 2019. We will learn about all of the benefits of XLOOKUP and why it's better than VLOOKUP. We'll do an exact match search by looking up days of the week from a list. We'll do a range lookup (approximate search) by looking up student grades.

Lesson 3 continues with XLOOKUP. We will see how to return multiple values from a single lookup, what to do if the value isn't found (match not found), and we'll learn about wildcard searches (*, ?, ~). We'll also take a look at the new XMATCH function.

Lesson 4 concludes our look at XLOOKUP. We will learn about two-way (double) lookups where you can actually look up values from a grid. For example, if you have a sheet with clothes and their sizes, and each combination has a different price. XLOOKUP can handle that. We'll also see an example of an approximate search where we can look for a student's letter grade in a grid given the subject, and the scoring criteria for that class.

So that's what's covered in Excel Expert Level 11. Plus, of course, there are lots of little tips and tricks thrown about here and there in the lessons (too many to list here). This will probably be the last of the Expert levels. Next I'm going to start getting into the Developer levels with macros, UserForms, and VBA programming. If you have any questions about whether or not this class is for you, please feel free to post your comments below, or contact me directly.

Complete Outline - Excel Expert 11

00. Intro (4:37)

01. Video Time Conversion (27:39)
Separating Text Strings
FIND, LEFT, LEN, MID, RIGHT
Converting to Seconds
Adding Times
QUOTIENT, MOD, TEXT Functions
How Many Hours in X Seconds
Force 2 Digit Minute, Sec 00:00

02. XLOOKUP Part 1 (13:28)
Benefits of XLOOKUP
Look up to the left or right
Return multiple results
Vertical or horizontal lookups
Reverse search (last to first)
Defaults to an exact match
Supports wildcards (*, ?, ~)
Sspecify a value if not found
Return entire columns or rows
Can work with arrays
Review of VLOOKUP
Weekday Lookup
Absolute References F4
Still works if you move ranges
Student Grades
Grade Lookup Table
List doesn't have to be in asc order
Exact Match Lookup
Approximate Lookup (Range)
Match Mode: Exact Match or Next Smaller Item

03. XLOOKUP Part 2 (11:13)
Returning multiple values
match_mode
Match Not Found: if_not_found argument
Wilcard Searches * ? ~
search_mode
Binary Searches - discussion
XMATCH

04. XLOOKUP Part 3 (16:48)
Two-way lookups (double lookup)
Clothes, Sizes, Look up Price
Course, Grade, Look up Letter
BONUS: Developer Tab
Combo Box
Input Range
Cell Link

05. Review (2:49)

Learn With Me

Hi. My name is Richard Rost. 

I'm the owner and president of Excel Learning Zone. I've been teaching Excel in the classroom since the early 1990s, and online since 2004. I was given the Microsoft MVP award in 2014, 2015, and again in 2023. I am the author of The Complete Idiot's Guide to Microsoft 2010 by Alpha Books (a division of Penguin).

I know Excel. More importantly, I know how to teach Excel. I have over 30 years of experience teaching Excel both in the classroom and online. My video lessons are the culmination of my knowledge and I guarantee you won't find better training for the price... anywhere.

Not sure? You can watch my Excel Beginner Level 1 course right here on my web site, absolutely free of charge. It's an hour-and-a-half of great solid material for anyone who wants to start learning Excel. More importantly, it will let you know if my videos are right for you.

Then, when you're finished with Level 1, you can order Excel Beginner Level 2 for just one dollar. That's right... only $1. Why? I'm almost giving it away because I know once you try my lessons you'll be hooked and want to come back for more.

Your satisfaction is guaranteed. If you have any questions about which lesson is right for you, please feel free to drop me an email any time, or post your comments below. I do my best to get back to people as quickly as possible.