Hello, data friends! Today I will walk you through how to use the vlookup function in excel. First, we will lay out how to do a vlookup, and then afterward we will dive into how to use the vlookup in your day-to-day work. The first step is the technical overview, while the second step is a conceptual overview.
Remember to keep in mind that the vlookup function in Excel is used to bring data from one table over to another similarly related table.
The vlookup formula in Excel in comprised of 4 arguments:
• The lookup value
• The lookup range
• The column index
• The approximate match option
Let’s use an example to visualize these 4 arguments. We will work with the data that is pictured below.
The two tables that are pictured are relatively similar because they both share a column that records different people’s names. The two tables differ in that the table to the left has a column with each person’s favorite NFL team while the table to the right has a column that lists each person’s age. If we want to bring over the age detail from the right table to the table that has the team detail, then we need to know how to use Excel’s vlookup function because the vlookup formula helps us combine data from different tables.
We already laid out the arguments for the vlookup, so let’s look at a visual representation of those arguments next.
I color-coded some of the fields to help make them easier to follow.
• The lookup value is in column A, which has the green highlight above it
• The lookup range is the entire range from column F to column G, highlighted in yellow
• The column index is numbered at the top of each column in the lookup range, highlighted in pink
• The approximate match option isn’t visualized as its either a TRUE/FALSE or 1/0 input
As far as the technical work goes, this info is just enough to help you understand the arguments when you want to do a vlookup. If you want to walk through the execution of our vlookup example and learn a bit more about the conceptual side of the vlookup, then continue reading below!
The vlookup configuration window helps make vlookups easy to write. Type “=” into your cell and then click on the function name to access the window, just like pictured below. If you see a formula other than the vlookup you should be able to scroll down the function list until you see the vlookup.
After you click where the red arrow show, you should see the menu pop up just like in the screenshot below.
This configuration menu takes the inputs you place into the argument boxes and adds in the formula name, commas, and parenthesis where needed. Over time you realize that it becomes a time saver.
The purpose of the vlookup function is to bring data from one table to another table. It’s easier to learn how to use vlookups if you keep this concept in mind while we walk through the steps.
If we had to look up the age values from the right table over to the left table, our process might look something like this:
When we want to look up the age from the right table to the left table, the first thing we do is look at the names in our left table. Next, we scan the name column in the right table until we find a matching name. Once we have the row with the matching name, we slide over to the right on that row until we find the age value we’re looking for.
This process is how Excel’s vlookup works too. The only difference is Excel needs the arguments filled out to understand what we’re looking for and where to look for it as it runs the above process.
When you bring data from two tables together, using a vlookup, both tables must share a similar column. This similar column is like a key that tells us when a row in one table is similar to a row in the other table. This key is what’s called the lookup value in Excel’s vlookup formula.
In our example data, both tables share the Name column in common. This is our key and what helps us to identify similar rows between the tables. When we ran through the lookup process, we scanned the name in the right table until we found a matching name value.
When we write out the argument we have to provide the lookup value as the one that we’re currently looking up in the left table. In our example, if we start writing out the vlookup formula in Cell C5, then we need to set our lookup value as Cell A5.
The lookup range tells Excel where to look for and what to look for. You can think of this as simply, look in this table for this column value. What’s critical to keep in mind during this step is that the first column in the lookup range argument needs to be the column where we want Excel to scan for the lookup value. This also implies that the column value we want to lookup has to be to the right of the lookup column. You cannot do lookup column values that are to the left of the lookup column.
In our example, the lookup range is the entire right table from column F to column G. The Name column (our lookup column) is the first column in our lookup range, while the Age column (our column value) is to the right of the Name column. Our argument looks like this:
The column index tells Excel which column in the lookup range has the value we’re looking for. We are looking for the Age value, which is column index # 2. All we need to do is set the column index argument to 2 and Excel will understand what value to pull from the matching row.
In cases where the column value we’re looking for is not the second column, then you simply swap the 2 to whichever corresponding column number. The first column in the lookup range is always index #1. Each column to the right of that increases the index by a value of 1. Below is an example of a table that has more than 2 columns.
If the above was our lookup table, then we would set the column index to 4.
This argument can be set as either TRUE/FALSE or 1/0, where FALSE = 1 and TRUE = 0. When you want an approximate match then you should use TRUE or 1, when you want an exact match you should use FALSE or 0. We want an exact match so we will set our value to 0. This argument can get complex when you’re working with the approximate match. I won’t delve into that in this post as I believe that requires a post on its own. I always run with 0 or FALSE as I always want an exact match from one table to the other.
Our final vlookup statement should look like the below:
Notice how the green box shows a 35. This 35 is the value that will be looked up given the arguments we provided in the configuration menu. A side note, make sure your configuration menu (in the red box) matches the formula bar (in the blue box); otherwise, you might throw an error.
Finally, you can hit OK or enter and the formula should execute. Our output looks like the below:
We can copy the formula we wrote in cell C5 (underlined in red) down to the end of the left table. The left table now has all the age values looked up from the right table. Our left table looks like the below:
We can audit our vlookup as a precautionary step to make sure that we input the arguments correctly. Below is an audit:
As you can see, the Age for each person has been successfully looked up from the right table to the left table.
The application of the vlookup in our example is very basic, we brought over one value from two tables that are both very limited in their scope. The vlookup formula can still be used in Excel for cases where there two tables at hand have an expansive set of columns. You can even use Excel’s vlookup formula to lookup data from tables that are on different sheets. The flexibility is there. The most important learning here is that vlookup allows us to combine data from different tables so that we can have a more robust data set for analysis.
This wraps up how to use the vlookup function in Excel. If you have any questions or need clarification don’t hesitate to reach me through my DMs on Twitter @Cest_Nick. I’ll catch you on the next post!
-Nick
