Introduction

Welcome! VLOOKUP, HLOOKUP & Lookup Tools

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

Welcome to Excel Expert Level 4. In this course we will focus on popular lookup functions, including VLOOKUP, HLOOKUP, LOOKUP, MATCH, INDEX, ADDRESS, and INDIRECT, and discuss how you can use them to retrieve data from different parts of your workbook. We will cover using VLOOKUP for range and exact match lookups, building an employee timesheet, working with cell reference functions, comparing lists, and finding the closest value in a list. This course is designed for users who have completed previous Excel Expert levels and are comfortable with logic functions introduced earlier.

Navigation

Prev: X04
Next: Lesson 01

Keywords

TechHelp Excel, VLOOKUP, lookup functions, HLOOKUP, MATCH, INDEX, ADDRESS, INDIRECT, Excel 2010, Excel expert, compare lists, range lookup, exact match, employee timesheet, find closest value, logic functions, IF function, IS functions

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Excel 2010 Expert, level 4, brought to you by ExcelLearningZone.com. I am your instructor, Richard Rost. In today's class, we will learn many of the popular lookup functions.

Lookup functions are used to look up a value from a different part of your workbook. We will learn several different types of lookup functions, including the lookup, HLOOKUP, lookup, MATCH and INDEX, ADDRESS, and INDIRECT.

We will use the VLOOKUP function, for example, to find the student's letter grade based on his final average. Plus, we will cover many other examples for the other lookup functions.

This class is designed to be used with Excel 2010, part of Microsoft Office 2010. However, even if you are using an older version of Excel, most of the topics in today's class, if not all, will work just fine with every version of Excel, back to at least Excel 2000.

This is an expert-level course for Excel 2010. This class was designed for the user who has a good amount of experience with Excel, preferably, who has taken my basic courses one through five, and the first three expert-level courses.

The prerequisite for this course is Excel Expert Level 3, which talks about logic functions. You will learn TRUE, FALSE, AND, OR, and NOT. The IF function, which is very important. If you have not learned the IF function yet, you should watch Expert Level 3. Plus the IS functions. We will be using a couple of these in today's class.

My courses are broken up into four different groups: beginner, expert, advanced, and developer.

The beginner courses are for novice users with little or no experience with Excel. The expert series, which is what you are watching right now, is designed for more experienced users who are already comfortable with Excel. Expert classes go into a lot more depth about each topic than the beginner classes did, and we will cover more functions, features, tips, and so on.

When you have mastered the expert classes, move up to the advanced lessons. You will learn how to build macros, build user forms, create your own templates, and many more advanced features that not everyone will use, but they really add enhanced functionality and professionalism to your spreadsheets.

Finally, my developer series is designed to teach you how to program in Visual Basic for Applications with Microsoft Excel. This will allow you to create Excel-based programs for your users, automate your spreadsheets, and integrate Excel tightly with the other Office applications.

Each of my series is broken down into different levels. For example, the beginner series contained five different levels, which you should have watched previously. This class is the fourth level of the expert series. Each level teaches you new and different topics in Microsoft Excel, building on the lessons learned in the previous levels.

When you have finished all the expert classes, move up to the advanced series, then finally, the developer series.

Now, I will start a more detailed look at exactly what we are going to learn in today's class.

In lesson one, we are going to learn about lookup functions. We are going to start with one of my favorite lookup functions, VLOOKUP.

In lesson two, we are continuing with VLOOKUP. In this lesson, we will perform a range lookup where we will look up the student's numeric grade and return a letter value.

In lesson three, we continue our look at the VLOOKUP function. We will perform an exact match lookup and we will build an employee timesheet.

In lesson four, we will take a look at some of the other lookup functions, including HLOOKUP, LOOKUP, MATCH, and INDEX.

In lesson five, we are going to learn some cell reference functions, ADDRESS, and INDIRECT.

In lesson six, we will learn how to compare two lists to see if an item in lesson one is missing from list two.

In lesson seven, we will use a combination of functions to figure out what the closest value is to a target number in a list of numbers.

If you need help with the topics covered in today's lessons, please feel free to post your questions in the Excel Interactive Student forums. If you are watching this course using my custom video player software or online in my web theater, you should see the student form for each lesson appear in a small window next to the class videos if you have an active internet connection.

Here, you will see all the questions that the other students have asked as well as my responses to them and the comments that some of the other students may have made. I encourage you to read through these questions and answers as you start each lesson. Feel free to post your own questions and comments as well.

If you are not watching your lessons online, you can still visit the student forums later by visiting excellearningzone.com/forums.

To get the most out of this course, I recommend you sit back, relax, and watch each lesson completely through once without trying to do anything on your computer. Then, replay the lesson from the beginning and follow along with my examples. Actually create the same spreadsheet that I make in the video. Build the spreadsheet with me step by step.

Do not try to apply what you are learning right now to other projects until you have mastered the sample spreadsheet. If you get stuck or do not understand something, watch the video again from the beginning or tell me what is wrong in the student form.

Most importantly, keep an open mind. Excel might seem intimidating at first, but once you get the hang of it, you will see that it is really easy to use.

Quiz

Q1. What is the primary purpose of lookup functions in Excel?
A. To find values in different parts of your workbook
B. To create new workbooks
C. To insert charts and graphs
D. To change the font of your spreadsheet

Q2. Which of the following is NOT mentioned as a lookup function in this lesson?
A. VLOOKUP
B. HLOOKUP
C. SUMIF
D. INDEX

Q3. What prerequisite knowledge is recommended before taking this expert-level course?
A. Knowledge of logic functions like IF, AND, OR, NOT
B. Experience with advanced macros
C. Familiarity with PowerPoint presentations
D. Experience with SQL databases

Q4. Which Excel function is specifically mentioned for returning a corresponding letter grade based on a student's numeric score?
A. VLOOKUP
B. ADDRESS
C. INDIRECT
D. CONCATENATE

Q5. After completing the expert series, what is the next suggested set of Excel courses?
A. Advanced series
B. Beginner series
C. Developer series immediately
D. PowerPoint integration lessons

Q6. What is the main difference between the beginner series and the expert series in the course structure?
A. Expert series covers more advanced topics in greater depth
B. Beginner series requires knowledge of VBA
C. Expert series only covers printing spreadsheets
D. Beginner series focuses on database connectivity

Q7. Which function will you learn for comparing two lists for missing items?
A. Functions that compare differences between two lists
B. Functions that sort data alphabetically
C. Functions that automatically email results
D. Functions that import data from PDF files

Q8. What is the suggested way to get the most out of the lessons?
A. First watch, then repeat the lesson and follow along creating the same spreadsheet
B. Only read the transcripts without using Excel
C. Skip directly to the final quiz
D. Apply the techniques immediately to your own unrelated spreadsheets

Q9. Where can students discuss questions or view other students' questions and answers?
A. In the Interactive Student Forums on the website
B. Through direct email support only
C. In-person classes only
D. Printed manuals mailed to your home

Q10. What advice is given if you get stuck or do not understand something in a lesson?
A. Watch the video again or ask for help in the student form
B. Ignore the topic and move to the next lesson
C. Try to fix it using internet search only
D. Uninstall and reinstall Excel

Q11. What is the focus of the developer series of Excel lessons?
A. Programming in Visual Basic for Applications to automate and integrate Excel
B. Learning to create pie charts and graphs
C. How to use Microsoft Access reports
D. Formatting PowerPoint slides from Excel data

Q12. Which function is NOT being covered in this specific class?
A. COUNTIF
B. MATCH
C. INDEX
D. INDIRECT

Answers: 1-A; 2-C; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-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 part of my Expert Level 4 course for Excel 2010. My name is Richard Rost, and in this lesson, I'll be teaching you all about some of the most popular lookup functions that Excel has to offer.

Lookup functions are vital when you need to pull a value from another area of your workbook. We're going to cover several different lookup functions including LOOKUP, HLOOKUP, VLOOKUP, MATCH, INDEX, as well as some useful cell reference functions such as ADDRESS and INDIRECT.

One of the first things we'll cover is the VLOOKUP function. I will show you exactly how to use VLOOKUP to determine a student's letter grade from their final average. We'll also work through a number of examples using all of the other lookup functions I just mentioned.

This class was created specifically for Excel 2010, which is part of Microsoft Office 2010, but do not worry if you're using an earlier version of Excel. Nearly everything discussed in this lesson applies to versions all the way back to Excel 2000.

You should know that this is an expert-level course. It's best suited for users who already have a good deal of experience with Excel and who have completed my beginner courses, levels one through five, along with the first three expert-level classes.

You are expected to have completed Expert Level 3, which focused on logic functions like TRUE, FALSE, AND, OR, NOT, and the very important IF function. If you're not already familiar with the IF function, I highly recommend going through Expert Level 3 first. We'll also be using some of the IS functions in this class.

My Excel courses are organized into four main groups: beginner, expert, advanced, and developer. Beginners are for those new to Excel, while the expert level is designed for people who already feel pretty comfortable using the program and want to learn in much more depth. Once you've finished the expert series, you can move on to the advanced lessons where you'll learn how to create macros, build user forms, design your own templates, and unlock even more powerful features. Finally, the developer series digs into programming with Visual Basic for Applications, which lets you build automated Excel solutions and integrate with other Office applications.

Each series is broken down into levels, and this class is the fourth level in the expert series. Every level introduces new and challenging topics, building on the foundation you've created through the previous lessons.

Let me outline exactly what we'll be covering in today's class. In lesson one, I'll introduce different lookup functions, starting with VLOOKUP, which is one of my favorites. In lesson two, we'll go deeper with VLOOKUP and learn how to look up a numeric grade to return the appropriate letter grade. Lesson three continues our exploration of VLOOKUP, this time focusing on an exact match lookup, and together we'll build an employee timesheet.

Lesson four will delve into other important lookup functions such as HLOOKUP, LOOKUP, MATCH, and INDEX. In lesson five, we'll explore cell reference functions like ADDRESS and INDIRECT, which are great tools to have in your toolkit.

Lesson six will show you how to compare two lists to see if items in one are missing from the other. And in lesson seven, I'll show you how to find the closest value to a given target in a list of numbers using a combination of functions.

If you have questions about anything covered in today's class, I encourage you to use the Excel Interactive Student forums. If you're using my custom video player or watching on the web theater, you should see a form next to the class videos where you can post your questions and interact with fellow students. Reading through existing questions and answers is a great way to reinforce what you've learned. Even if you're not watching online, you can visit the forums on my website later.

To really get the most from this course, I'd suggest watching each lesson once all the way through before you try anything on your own computer. Then, start the lesson over and follow along step by step as I build out the sample spreadsheets. Stick with the provided examples until you feel comfortable before applying the concepts to your own projects. If something is not clear, rewatch the lesson or let me know through the student form.

Most importantly, try to keep an open mind. Excel can seem intimidating at first, but once you get used to these tools, you'll discover how easy - and powerful - it really is.

If you want a complete video tutorial with step-by-step instructions for everything we've discussed here, visit my website at the link below.

Live long and prosper, my friends.

Topic List

Introduction to lookup functions
Overview of VLOOKUP function
Range lookup with VLOOKUP for letter grades
Exact match lookup with VLOOKUP
Building an employee timesheet with VLOOKUP
Using HLOOKUP function
Using LOOKUP function
Using MATCH function
Using INDEX function
Cell reference functions: ADDRESS
Cell reference functions: INDIRECT
Comparing two lists for missing items
Finding the closest value to a target number in a list

Article

Lookup functions are a powerful part of Microsoft Excel that allow you to find and retrieve data from different parts of your workbook quickly and efficiently. Whether you need to match a student's score to a letter grade, pull employee information for a timesheet, or analyze large lists of values, lookup functions can save you a lot of time and make your spreadsheets much more dynamic.

One of the most used lookup functions is VLOOKUP. With VLOOKUP, you can look up a value in a table and return information from another column in that table. For example, if you have a list of students with their final average grades in one column, and another column with the letter grades, you can use VLOOKUP to automatically assign the correct letter grade based on each student's average. The basic syntax for VLOOKUP is VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Here, lookup_value is what you want to look for, table_array is the range of your data, col_index_num specifies which column has the value you want to return, and [range_lookup] tells Excel whether to look for an exact match or an approximate one.

Suppose you want to assign a letter grade to a student based on their numeric score. You could set up a table listing the minimum score for each grade. Then, you would use VLOOKUP to find the correct letter grade for a given score. If your table looks like this:

90 A
80 B
70 C
60 D
0 F

and the student's score is in cell B2, your formula would look something like:
=VLOOKUP(B2, $E$2:$F$6, 2, TRUE)

In this example, the formula finds the correct grade based on the student's score. The TRUE parameter at the end tells Excel to find the closest match that is not greater than the lookup value.

VLOOKUP also works for exact matches. For instance, if you have an employee timesheet where you need to pull an employee's role based on their ID, you would set up a small table with employee IDs and their roles, and then use VLOOKUP with the FALSE parameter to require an exact match. The formula might look like:
=VLOOKUP(A2, $D$2:$E$10, 2, FALSE)

This would return the employee's role for the ID entered in cell A2.

While VLOOKUP is very popular, there are other useful lookup functions in Excel. HLOOKUP works just like VLOOKUP, but searches horizontally across the top row instead of vertically down the first column. The syntax is almost identical, just with rows and columns switched.

The LOOKUP function is another way to search for a value. Although a bit more flexible, it is considered less powerful than VLOOKUP and HLOOKUP because it does not offer the same options for exact and approximate matches, but it's still handy for simple lookups.

For more complex tasks, the combination of MATCH and INDEX provides greater flexibility. MATCH returns the position of a value in a list, and INDEX returns the value at a specific position in a range. Let's say you have a list of names and you want to find the salary for a specific person. First, use MATCH to find the row where the name appears, then use INDEX to retrieve the salary from the same row in a different column.

For example:
=MATCH("Smith", A2:A10, 0)
returns the position of Smith in column A.

Then,
=INDEX(C2:C10, MATCH("Smith", A2:A10, 0))
returns the corresponding salary in column C.

Cell reference functions like ADDRESS and INDIRECT help when you need to construct or refer to cell addresses dynamically. ADDRESS returns the address of a cell based on row and column numbers. For instance:
=ADDRESS(2, 3)
returns $C$2.

INDIRECT takes a cell reference as text and returns the value in that cell. If cell A2 contains the text B5 and cell B5 has the value 100, then:
=INDIRECT(A2)
returns 100.

These functions are especially useful when you need to build formulas that refer to different sheets or variable ranges based on user input.

You may also encounter situations where you want to compare two lists to see if anything is missing from one list or the other. In these cases, functions like MATCH or ISNUMBER combined with IF can help highlight differences. For example, to check if a value from List A exists in List B, you might write:
=IF(ISNUMBER(MATCH(A2, $D$2:$D$20, 0)), "Yes", "No")

This will display "Yes" if the value in cell A2 is found anywhere in D2:D20.

Sometimes you need to find the closest value to a particular target in a list of numbers. You can achieve this with a combination of ABS, MIN, and MATCH. For example, to find the value closest to 50 in range A2:A20:
=INDEX(A2:A20, MATCH(MIN(ABS(A2:A20-50)), ABS(A2:A20-50), 0))

Remember to enter this as an array formula if required by your version of Excel, which means pressing Ctrl+Shift+Enter not just Enter.

As you can see, lookup functions open up many possibilities for dynamic and interactive spreadsheets. They are foundational tools for anyone working seriously with Excel. If you run into questions or get stuck, take some time to revisit the basics using sample data and be sure you understand how the formulas reference ranges and values. Once you get comfortable, you will be able to apply these functions to almost any situation, making your spreadsheets smarter, faster, and more efficient.