Introduction

Welcome! Video Time, XLOOKUP & Combo Box

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

Welcome to Microsoft Excel Expert Level 11. In this course we will cover video time conversion using text functions like FIND, LEFT, RIGHT, MID, and division to reformat time data, and we will discuss the new XLOOKUP function, including its features that replace VLOOKUP and HLOOKUP, as well as how to use XLOOKUP for multiple values, match modes, wildcard searches, and two-way lookups. We will also look at enabling the Developer tab and adding combo boxes to your spreadsheets for user selections. Prerequisites and forum resources are also discussed.

Navigation

Prev: X11
Next: Lesson 01

Keywords

TechHelp Excel, XLOOKUP, video time conversion, Excel combo box, Excel 2019, double lookup, Developer tab, match mode, wildcard search, VLOOKUP limitations, convert time text, Excel Office 365, Excel VBA, macro programming

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Microsoft Excel Expert Level 11 brought to you by ExcelLearningZone.com. I am your instructor, Richard Rost, author of the Complete Idiot's Guide to Excel 2010.

In today's class, we are going to talk about a couple of different topics.

First, we are going to go over something I call video time conversion. I have got a list of videos with times in basically a text file, and I am going to teach you how to break them up and convert them into a different format.

Now this is just a simple example that takes a lot of different things that we learned over the last bunch of classes and puts them all together. You will see what I mean when we get into that lesson. There is FIND, LEFT, RIGHT, MID, division. There is all kinds of crazy stuff.

Then the big topic is XLOOKUP. This was recently added to Excel 2019. It was actually added in March of 2020. So if you have not updated Excel, you may or may not have this yet. But it is a great function. It replaces both VLOOKUP and HLOOKUP, and it has got lots of new features and you will see what those are in just a few minutes.

And then pretty much a bonus feature. I am going to break open the Developer tab, and I am going to teach you how to put combo boxes in your spreadsheet so your users can pick from a list of options and store that value that is selected in a cell in your spreadsheet. I normally cover that in my Developer lessons because it is a little more advanced, but I am going to show you today because it just kind of fits.

I am using Microsoft Excel 2019, and lesson one will work with pretty much any version of Excel. But the XLOOKUP stuff especially only works in 2019. I have Office 365, which is a subscription. I strongly recommend that. That way you always have the latest version of Excel automatically installed, downloaded, and set up on your computer for you.

The prerequisite to this course is my Excel 2010 Expert Level 10 class. Now I know it says Excel 2010, but it has been a while since I have released a new Excel class. However, up until recently Excel really has not changed much.

There is a lot of new stuff that has come out in 2019, including some feature changes. They did some modifications with charting and stuff that I am going to go over. I am going to make a series of upgrading two lessons to cover some of the differences. But if you have got 2007 and later, you should be able to figure out most of the changes, again with the exception of XLOOKUP, which is new.

If you have never taken any of my classes before, I have for Excel, I have beginner, expert, and developer lessons. Beginner, I believe there were five levels; expert, there are now 11; and pretty soon I am going to be starting the developer series. Developer focuses on macros and VBA programming.

Now I strongly recommend you check the Excel forums. Depending on where you are watching this video, you may see the forum for this class appear below the video. If not, go to my website, find nincd.com/forums, and you will find the Microsoft Excel forum there. Check the forum for this class specifically because there may be updates or notifications or other things, and you can post any questions that you have there and either myself, one of the forum admins, or even some of the other students might answer your questions for you.

Let us get started and talk about what is covered in today's class.

Lesson 1 is going to talk about video time conversion. Basically, I have got a list of videos, basically files that have a time index next to them, 12 minutes, 13 seconds for example, and I am going to flip them over to a different format. I have got to dissect that string, pull the time portion out, and do some crazy math and stuff with it. The intro to Lesson 1 will explain in a lot more detail what this covers.

Lesson 2 is going to begin looking at XLOOKUP, the new function in Excel 2019. XLOOKUP has gotten rid of a lot of the limitations of VLOOKUP and we will talk about what all those are. We will do some simple XLOOKUPs.

In Lesson 3, we are continuing with XLOOKUP. We are going to learn about returning multiple values, match mode, wildcard searches, and a lot more.

Lesson 4 concludes our look at XLOOKUP with a look at two-way lookups, also called double lookups because it is an XLOOKUP inside of another XLOOKUP. I am also going to break open the Developer tab, which is in Excel but it is hidden. We will turn that on and I will show you how to use a combo box, which you can see right there in the screen, where the user can pick from a drop down list and that value is saved in a cell which you can then use for your calculations.

Quiz

Q1. What is the main purpose of the video time conversion lesson?
A. To convert text-based video times into a different format for use in Excel
B. To edit video files directly within Excel
C. To create charts of video durations
D. To download videos from the internet

Q2. Which Excel functions are specifically mentioned as being used in the video time conversion lesson?
A. FIND, LEFT, RIGHT, MID, division
B. SUM, COUNT, IF, MAX
C. TRIM, CONCATENATE, SUBTOTAL, TODAY
D. AVERAGE, LARGE, SMALL, RAND

Q3. What important new function is discussed in this class?
A. XLOOKUP
B. DGET
C. INDEX
D. CHOOSE

Q4. What does XLOOKUP replace in Excel?
A. Both VLOOKUP and HLOOKUP
B. Only SUMIF
C. Only INDEX and MATCH
D. Only LOOKUP

Q5. What limitation does XLOOKUP remove compared to previous lookup functions?
A. It can look both vertically and horizontally without separate functions
B. It only works for numbers
C. It cannot search for text values
D. It only works for one cell at a time

Q6. What feature does the instructor introduce from the Developer tab?
A. Combo boxes to let users select from a list
B. Conditional formatting rules
C. Creating pivot tables
D. Inserting pie charts

Q7. What is suggested if you want to always have the latest version of Excel?
A. Subscribe to Office 365
B. Use only Office 2007
C. Avoid all updates
D. Download free alternatives

Q8. What prior course does the instructor recommend as a prerequisite for this lesson?
A. Excel Expert Level 10
B. Excel Beginner Level 1
C. Access Developer Level 1
D. PowerPoint Advanced Level 2

Q9. What additional support resource does the instructor suggest for students with questions?
A. The Excel forums on his website
B. Calling Microsoft support directly
C. Asking questions on social media
D. Emailing random strangers

Q10. What can you achieve by using a combo box from the Developer tab in Excel?
A. Allow users to pick from a dropdown and store the selection in a cell
B. Display a list of formulas used in the workbook
C. Automatically generate a bar chart
D. Share the file directly via email

Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-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 is Expert Level 11, and I'm your instructor, Richard Rost. In this session, I have a few advanced Excel topics to cover that build on skills from previous classes.

We'll start with what I call video time conversion. I've got a list of video files, each with time indications next to them like "12 minutes, 13 seconds," typically stored in a text format. I'm going to walk you through the steps involved in extracting those time portions and converting them into a format that's more useful for calculations. This may seem simple, but it actually requires you to apply knowledge from a variety of Excel functions and techniques you've learned so far, such as FIND, LEFT, RIGHT, MID, and some arithmetic. We'll tackle all that in our first lesson.

The main focus today is on XLOOKUP, which was introduced in Excel 2019, specifically in March 2020. If you have an older version, you may not have this function yet, but it's a powerful upgrade because it replaces both VLOOKUP and HLOOKUP. XLOOKUP comes with several improvements and new features over its predecessors, and I'll break down exactly what those are and how you can use them in your everyday work.

As a bit of a bonus, I'm also going to introduce you to combo boxes from the Developer tab. With this control, you can let users select from a dropdown list, and their selection gets stored directly in a cell of your worksheet for further calculations. This topic is a little more advanced and usually covered in my Developer-level tutorials, but it makes sense to include here since everything ties together nicely today.

I'm working in Microsoft Excel 2019 for this course, so Lesson 1 will be useful for almost any version of Excel. The XLOOKUP demonstrations, however, will only work in Excel 2019 or newer. Personally, I use Office 365, and I strongly recommend using the subscription version so you always have the latest features downloaded and ready to go automatically.

The prerequisite for this class is my Excel 2010 Expert Level 10 course. Even though it says Excel 2010, not much changed in Excel until recent years, so those earlier lessons still apply, especially up to and including Excel 2016. Many of the major differences come with the 2019 version, including several enhancements to charting, which I'll discuss in future upgrading lessons. If you are using Excel 2007 or later, you should still be able to follow along with most of what I'll teach, except for XLOOKUP, which is exclusive to 2019 and later.

If you are new to my program, I offer a full series of lessons for different Excel skill levels, including beginner, expert, and soon, developer. The developer series will focus more on macros and VBA programming. If you have any questions as you go through the material, I recommend checking the Excel forums. You can visit my website at nincd.com/forums to find the specific forum for this class. You might find updates, answers to common questions, or you can post your own, and either I, my forum admins, or other students will be able to help you out.

Now, let's look in more detail at what today's class will cover.

Lesson 1 focuses on video time conversion. If you have a list of video files with times recorded in minutes and seconds, I'll show you how to break apart those strings, extract the numbers, and convert them into total seconds or another useful format using a combination of Excel functions and a bit of math.

Lesson 2 introduces XLOOKUP, the new lookup function. We'll discuss how XLOOKUP corrects many shortcomings of VLOOKUP and do some straightforward lookups as examples.

Lesson 3 continues with XLOOKUP, extending to advanced uses like returning multiple values, changing the match mode, performing wildcard searches, and exploring additional features.

In Lesson 4, we'll see how to do two-way or double lookups, which involves nesting one XLOOKUP inside another. At the end of the course, I'll also walk you through enabling the Developer tab and creating a combo box so a user can make a selection from a dropdown list. This choice then automatically updates a cell in your worksheet so you can use it later in your calculations.

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

Video time conversion techniques
Extracting and converting time strings
Using FIND, LEFT, RIGHT, and MID functions
Applying division for time calculations
Introduction to XLOOKUP
Replacing VLOOKUP and HLOOKUP with XLOOKUP
Basic XLOOKUP functionality
Returning multiple values with XLOOKUP
Match mode and wildcard searches in XLOOKUP
Two-way (double) lookups with XLOOKUP
Enabling and using the Developer tab
Adding and configuring combo boxes
Storing combo box values in worksheet cells

Article

Welcome to this advanced Microsoft Excel tutorial, where I will guide you through some practical and powerful features that can take your skills to the next level. Today, we will cover three main topics: converting video time strings, using the XLOOKUP function for advanced lookups, and enhancing your spreadsheets with combo boxes from the Developer tab.

Let us begin by tackling the process of video time conversion. Imagine you have a list of videos, each with its duration listed in a text format like "12 minutes, 13 seconds." These time values exist as plain text or strings and are not easily manipulated for calculations or sorting. Our goal is to extract these time values, convert them into a numeric format, and make them useful for analysis.

To break down these text strings, you might use text functions such as FIND, LEFT, MID, and RIGHT, alongside arithmetic operations. Suppose you have a time entry in cell A2 that reads "12 minutes, 13 seconds." First, you need to locate where the word "minutes" ends, isolate the number before it, and do the same for "seconds." Here is a sample formula to extract the minutes:

=VALUE(LEFT(A2, FIND(" minutes", A2) - 1))

This formula finds the position of the word "minutes," takes all the characters to the left of it, and converts the result to a numeric value. To extract the seconds, you can use:

=VALUE(MID(A2, FIND(",", A2) + 2, FIND(" seconds", A2) - FIND(",", A2) - 2))

This approach uses the MID function to find the position after the comma, then calculates the length up to the word "seconds." Once you have both values, you can perform calculations like converting the entire time into seconds or decimal minutes. For example:

=minutes*60 + seconds

or

=minutes + (seconds / 60)

This lets you compare, sort, and analyze your video times easily. This simple exercise uses a combination of skills such as string manipulation and arithmetic, which are essential when working with data that is not in perfect numeric format.

Next, let us dive into the powerful XLOOKUP function. This function was introduced to overcome the many limitations of the older VLOOKUP and HLOOKUP functions. XLOOKUP allows you to search a range or array for a match and return the corresponding value from another range. Unlike VLOOKUP, your data does not have to be sorted. XLOOKUP can look to the left or right, and you do not have to specify a column number. This function is incredibly straightforward yet flexible.

Suppose you have a table with product names in one column and prices in the next. If you want to find the price for a particular product, you would write:

=XLOOKUP("ProductName", A2:A100, B2:B100, "Not Found")

Here, "ProductName" is the value you are looking up. A2:A100 is the range to search, and B2:B100 is the range from which to return the result. If the product is not found, "Not Found" will be displayed. XLOOKUP also allows you to perform more advanced lookups, such as matching based on approximate values, returning multiple columns, or using wildcard matches. For example, if you want to find all entries starting with a certain letter, you could use a wildcard like "A*".

One of XLOOKUP's most powerful features is its ability to return multiple values and offer match modes, such as exact or nearest match. For returning multiple values, you can set the return_array parameter to cover multiple columns, and XLOOKUP will spill the results if your Excel version supports dynamic arrays. You can also tailor the match behavior with the match_mode parameter, specifying whether you want to find an exact match, the next smaller, the next larger, or use wildcards. For example, to enable wildcard matching, you can add a parameter like 2 for match_mode:

=XLOOKUP("A*", A2:A100, B2:B100, "Not Found", 2)

For more complex searches, such as two-way lookups, XLOOKUP really shines. A two-way lookup might involve finding a value at the intersection of a specific row and column, such as looking up a sales figure for a particular product in a specific month. In this case, you can nest an XLOOKUP within another XLOOKUP. For example, to get the value at the intersection of a product and a month:

=XLOOKUP("ProductName", A2:A100, XLOOKUP("MonthName", B1:H1, B2:H100))

This retrieves the sales for "ProductName" in "MonthName." Using this method, you can pull data from complex tables effortlessly.

Finally, let us enhance your spreadsheets with a user-friendly feature: the combo box. Combo boxes allow users to pick from a predefined list of options. This is especially useful if you want to limit data entry to certain values or make your spreadsheets easier to use. The combo box is one of the controls available on the Developer tab, which might be hidden by default. To enable it, go to your Excel options and select Customize Ribbon. Then, check the box for Developer.

Once the Developer tab is visible, click Insert, and choose Combo Box from the list of Form Controls. Draw the combo box on your worksheet. Right-click the combo box and select Format Control. In the Input Range field, specify the cell range that contains your list of options (for example, D2:D10). In the Cell Link field, pick a cell where the selected value will be stored. This cell will display the position number of the selected item, so you may want to reference this number with an INDEX function to display the actual value elsewhere.

For example, if D2:D10 contains a list of departments and your cell link is E2, then =INDEX(D2:D10, E2) will give you the department name selected from the combo box. This setup helps streamline data entry and ensure your calculations use only valid, user-selected data.

These techniques - converting string-based times, using XLOOKUP for advanced lookups, and adding interactive controls like combo boxes - will greatly enhance your Excel work, making your spreadsheets more dynamic, accurate, and easier for others to use. Practice these methods and you will not only improve your own productivity but also create more professional, user-friendly spreadsheets. If you have questions or want to discuss more advanced topics, explore popular online Excel forums or communities where fellow learners and experts can help you solve any stumbling blocks you encounter.