26 hours ago: New Feature: you can now upload your Profile Picture to the site. Check it out!  Dismiss
 
Excel 2010-2019
Excel 2007
Excel 2003
Tips & Tricks
Excel Forum
Course Index CIG Excel Book
 
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
 
Home > X11 >
Excel Expert 11

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.

Buy Now

Excel Expert Level 11
Versions: XLOOKUP is specific to Excel 365
Pre-Requisites: Excel Expert Level 10
Running Time: 1 hour, 16 minutes
Cost: $12.99
  Members get 50% OFF. Join Now!

 

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.

Buy Now

 

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.

 

Buy Now

Start a NEW Conversation
 
Only students may post on this page. Click here for more information on how you can set up an account. If you are a student, please Log On first. Non-students may only post in the Visitor Forum.
 
Subscribe
Subscribe to Excel Expert 11
Get notifications when this page is updated
 
 
 
 

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
PCResale.NET
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

LinkedIn
Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 3/18/2024 11:24:38 PM. PLT: 0s
Keywords: excel course x11 expert  PermaLink  Excel Expert 11