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.