How to use VLOOKUP in Microsoft Excel
One of the most useful, but daunting, spreadsheet skills for business must be how to use VLOOKUP in Microsoft Excel. It’s nature, in simplifying the search data in Excel, has become a mainstay for many spreadsheet users.
For anyone new to the VLOOKUP feature, it may seem overly technical and foreign. However, in just a few minutes time, anyone can get the hang of it. Once you do get it, you will likely experience that eureka moment so many people have experienced over the years since this game changing function arrived on the scene.
What exactly is a VLOOKUP? VLOOKUP is a built-in function in Excel and the name stands for “vertical lookup”. You may be thinking, “what is a ‘vertical lookup’?”.
It is called a “vertical lookup” because the formula takes a value and searches your data vertically down a specific column to find a possible match for your lookup value. When it finds that value, it stops and looks for a value on the same row in a column some number to the right that you specify.
VLOOK UP SYNTAX
Let’s look at the syntax and then jump into some examples to see VLOOKUP in action. Then you will get a better grasp on what it actually does.
‘=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])’
VLOOKUP has four total arguments. Three are required while the last is optional. Let us break each of them down and explain.
‘lookup_value’ – This is the value you want VLOOKUP to search for in the ‘table_array’. It is a required argument and if this value is not found in the ‘table_array’ it returns the ‘#N/A’ error.
‘table_array’ – This is the range of data that you want the VLOOKUP to search for a match to your ‘lookup_value’ and return a result from. It is also a required argument and must be at least as many columns wide as the ‘col_index_range’ value.
‘col_index_range’ – This is the number of columns from the first column in your ‘table_array’ which you want VLOOKUP to return a value. It is a required argument and will cause the formula to return a ‘#REF!’ error if it is a number greater than the number of columns in the ‘table_array’.
‘range_lookup’ – This argument is one of two values: TRUE or FALSE. Select TRUE for an approximate match or FALSE for an exact match. This is an optional argument and if omitted will default to an exact match.
A special note about the ‘range_lookup’ argument is that you can substitute ‘0’ for FALSE and ‘1’ for TRUE. They will achieve the same results and save you a few extra key strokes.
OUR FIRST EXAMPLE: LOOKUP INVENTORY AVAILABLE BY SIZE
Imagine a situation where you have a database listing current inventory levels by product size. We can demonstrate a simple example of how VLOOKUP works by setting up a lookup by size. This will in turn give us the current inventory available for any given size that we choose.
In the preceding figure, note that our database is the table in the range B2:C8. We will select this range for the ‘table_array’ argument in our VLOOKUP formula.
We have setup a lookup table in cells E2:F3. There is a dropdown list in cell E3 for convenience and its value serves as the ‘lookup_value’ argument in our VLOOKUP. The formula is in cell F3 and as you can see, it is returning the available inventory for the size selection in cell E3.
Now let us take a closer look at the formula itself. By double-clicking into cell F3, we can see the formula and Excel highlights the ranges for our ‘lookup_value’ and ‘table_array’.
This shows you that the VLOOKUP finds the size we select in our lookup table (E3) in the first column of our table array (B2:C8), and then returns the value from the second column of the table array on the row that matches.
This brings up a very important point about VLOOKUP that is somewhat of a limitation. The first column of your table array must be the column that VLOOKUP searches for your lookup value. This is the reference point from which the ‘col_index_range’ argument is based on.
You must be aware that you may have to manipulate a dataset to accommodate this limitation. If the data you want a VLOOKUP to return happens to be in a column to the left of your lookup column, you will first have to make sure you move it to the right or use alternative lookup functions such as INDEX and MATCH.
Something that might not be obvious at first is how useful the VLOOKUP can be when not simply looking up a value for its own sake. You can very easily include the VLOOKUP in a larger formula to use the value it returns within another formula.
OUR SECOND EXAMPLE: INTEGRATING THE VLOOKUP WITH OTHER FORMULAS
Imagine you are the HR Director for an organization and you want to create a simple lookup table that will allow you to index your employee database by employee ID. Here is a look at the actual database table.
You come up with the idea to create a lookup table that allows you to pull information from your database by the Employee ID. Specifically, we want to accomplish three things. First, we want to pull the employee name. Easy enough. That’s a simple VLOOKUP just like we saw in the first example.
We have set this up just like our size and inventory table example. Cell G3 is where we enter the ID we want to look up from the employee table, the range B2:D22 is the employee table, and the value 3 is the third column in that table.
The second thing we want to figure out from our database is how many years an employee as worked for the organization. The employee table tells us the hire date, but in order to find the length of employment, we need to make a calculation. No problem.
We can integrate the VLOOKUP with a calculation formula that uses the TODAY function as a reference point. All the TODAY function does is return today’s date. If we can subtract the hire date from the result of TODAY and then divide by 365, then we can get the result we want.
Note that our VLOOKUP is very similar to the previous figure except our ‘col_index_num’ argument value is 2. This is because we now want the hire date instead of employee name from the same lookup table range.
The last thing we would like to populate in our lookup table is the employee’s anniversary date. This time, we are still using the VLOOKUP to pull a result from column 2. However, this time we are wrapping it in the TEXT function.
The TEXT function allows us to reformat a date value so we are using it to output the long form of the month and the day from the hire date.
Note that the VLOOKUP is actually the ‘value’ argument for the TEXT formula. Now we have an employee lookup table that use the VLOOKUP in a variety of ways.
Our hope with this article is that not only may you learn how simple it is to create and use VLOOKUP formulas but also how useful they can be. With a bit of imagination, you can see how easy it is to create some elegant solutions. Especially when you begin to use the VLOOKUP within other formulas. Moreover, while VLOOKUP does have some limitations it is still the perfect fit in many situations.