Exceljet

Quick, clean, and to the point

This video comes from our online video training for Excel.

How to use VLOOKUP

Tags 
Summary 
In this video, learn how to use VLOOKUP to build a simple form to extract employee information from a table, in about 3 minutes.
Video Transcript 

VLOOKUP is one of the most important lookup functions in Excel. The V stands for "vertical" which mean you use VLOOKUP to lookup values in table that's arranged vertically.

Let's take a look.

Here we have a list of employees in a table. Let's use VLOOKUP to build a simple form that retrieves the information for a given employee based on their ID number.

The first thing I'll do is create a named range for the data in the table, and a named range for the id that we'll be using to look things up. By using named ranges, our VLOOKUP formulas will be easier to understand, and easier to copy.

We're going to lookup employees by id, so let's go ahead and enter a valid ID, so we can verify what VLOOKUP is finding as we enter the formulas. I'll use 869, for Julie Irons.

Now let's use VLOOKUP to get the first name.

VLOOKUP takes four arguments: the lookup value itself, the table to use for the lookup, the column number to use when retrieving a value, and finally, something called range_lookup.

In this case, the lookup value is the named range "id". Note that this value must appear in the left-most column of the table, since VLOOKUP looks only to the right.

The table is our named range "data".

For column, we need to provide a number that corresponds column number of the value we want in the table. First name is in column 2, so that's the number we need.

range_lookup is a confusing name for an argument, but it simply controls matching. If set to TRUE or 1 (which is the default value) VLOOKUP will allow a non-exact match. If set to zero or FALSE, VLOOKUP will require an exact match.

Unless you have a good reason to allow non-exact matches, you should always enter 0 or FALSE to require an exact match. Otherwise, VLOOKUP may find the wrong value.

We'll cover non-exact matching with VLOOKUP another video.

When I press return, VLOOKUP uses the ID to find the name Julie from the table.

Now I'll copy the formula down and adjust as needed.

For last name, I need to change the column number to 3.

For email, the column number is 4.

For department, the column number is 5.

And finally, for start date, the column number is 6.

Now I can enter any valid ID, and VLOOKUP retrieves the correct information from the table.

Author 
Dave Bruns

Related shortcuts

Your videos are the best one I have seen so far. I use excel in my advanced acc. techniques course and may be coming back to you for some tailored chosen set of videos. - Joanna
Excel foundational video course
Excel Pivot Table video training course
Excel conditional formatting video course
Excel formulas and functions video training course
Excel Shortcuts Video Course