Excel Crash Course: VLOOKUP, HLOOKUP, and LOOKUP Functions

Before starting with the Lookup functions, we'll start by reviewing what a function is.  Formulas are described, and Functions are introduced and detailed.

After discussing what Functions are, Chase will demonstrate four methods for entering Functions into Excel.  The methods are: into a cell, into the Formula Bar, using the Insert Function tool, and also AutoSum for a select few Functions.

Chase will introduce the concept of VLOOKUP and illustrate through example how the Function works.  A couple of examples are provided to show what types of actions can be performed and improved using VLOOKUP.

The first instance of VLOOKUP will be deployed in the form of finding an exact match form a table array.  This lesson walks through how to specify a lookup value, set a table array, select a column of data to return, and to set VLOOKUP to provide only exact matches.

This video introduces a downloadable Excel file for use with Lecture 5 (this lecture), Lecture 6, and Lecture 7.

Now that exact matches have been detailed, this lesson will walk through the use of approximate matches within VLOOKUP in order to enable calculating such things as discounts, commissions, or any other variable quantity through the use of tabular data and the VLOOKUP function.

This video uses the same file made available in Lecture 5: Finding Exact Matches.  The option to download again has been made available simply for convenience.

In the first truly hands-on challenge of the course, Chase will task the viewer with solving a stereotypical Excel problem using VLOOKUP.  After a brief pause, the solution to the challenge will be presented.  Attempt to complete the challenge while the video is paused, starting up again only to confirm your answer or to reveal help if stuck.

This video uses the same file made available in Lecture 5: Finding Exact Matches.  The option to download again has been made available simply for convenience.

Replacing nested conditional statements is one of the most important uses of lookup functions.  In this lesson, a step-by-step walk through will be provided to showcase exactly how to perform the transition from a nested conditional to a VLOOKUP.

This video introduces a downloadable Excel file for use with this lecture (Lecture 8).

HLOOKUP is introduced in this lesson as a minor variant upon VLOOKUP.  The primary difference is that VLOOKUP table arrays are structured in a way that is considered best practice for table layout.  HLOOKUP enables us to achieve the same results as VLOOKUP, only when the table array is transposed.

This video introduces a downloadable Excel file for use with this lecture (Lecture 9) and Lecture 10.

In the second hands-on challenge of the course, the viewer will be tasked with utilizing an HLOOKUP to account for a transposed table array. After a brief pause, the solution to the challenge will be presented.  Attempt to complete the challenge while the video is paused, starting up again only to confirm your answer or to reveal help if stuck.

This video uses the same file made available in Lecture 9: Introduction to HLOOKUP.  The option to download again has been made available simply for convenience.

LOOKUP is introduced and described as a slightly backwards and outdated function that is included in Excel for compatibility purposes.  Despite this negative omission, there are a number of uses for LOOKUP that are highly advantageous.  Examples of these situations will be provided.

This video introduces a downloadable Excel file for use with this lecture (Lecture 11), Lecture 12, and Lecture 13.

LOOKUP can be utilized in two ways, as a vector or an array.  Within this lesson, the vector style of LOOKUP will be utilized to identify data from a table array that is to the left of our lookup vector.

This video uses the same file made available in Lecture 11: Introduction to LOOKUP.  The option to download again has been made available simply for convenience.

While using LOOKUP as an array, it is possible to pull data from a second table array or range (or simply a disjointed table) as long as the lookup vector exists in the left-most column of both parts of the disjointed table. An example of this will be provided within this lesson.

This video uses the same file made available in Lecture 11: Introduction to LOOKUP.  The option to download again has been made available simply for convenience.

The third and final challenge of this course will task viewers with pulling information from one table into another.  The example is one that mimics a real-world scenario of importing and processing data from a database within Excel.

This video introduces a downloadable Excel file for use with this lecture (Lecture 14).

Data validation is a wonderful feature within Excel.  It is particularly useful in combination with VLOOKUP and the other lookup functions.  This lesson explains how to use List-style validation and how to pair that feature with VLOOKUP.

This video introduces a downloadable Excel file for use with this lecture (Lecture 15) and Lecture 16.

In the final lesson of the course, the use of tables is introduced--along with the INDIRECT function--as a way to create scalable data validation lists.  These lists can grow or shrink in real time based the values entered into a table's column.

This video uses the same file made available in Lecture 15: Drop down lists.  The option to download again has been made available simply for convenience.

Hopefully this course has provided much value to the viewer.  Chase encourages the viewer to stay in touch and to visit the ABGamma website to find out about other courses that are available to continue along a life-long path of learning.