How to use VLOOKUP

VLOOKUP is one of the most important lookup functions in Excel. The V stands for "vertical" which means you can use VLOOKUP to look up values in a 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 lookup formulas will be easier to understand and easier to copy.

We're going to look up 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 only looks to the right.

The table is our named range "data."

For column, we need to provide a number that corresponds to the 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 "0" 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 in another video.

When I press Enter, VLOOKUP uses the ID to retrieve the name "Julie" from column 2 of 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.