How to Master a VLookup in Excel

Microsoft’s Excel is a great tool to use when trying to complete analysis or collect data. By having a handle on a few simple formulas, you can easily organize your data. Allowing you to gain useful insights into your business.

The news is filled with stories about big data and analytics. Large companies such as IBM are changing the way data is used. All businesses whether they are big are small, whether they have access to the latest in technology or not have a ton of data. Being able to collect that data and make sense of it can change the way you do business and the way your business performs.

For a small business, using Excel is a great place to start making sense of your data. A few weeks back we looked at a few basic functions in Excel to get you started. Take a look here if you need a refresher (https://www.finergysolutions.com/single-post/2017/04/21/Learn-to-Use-Excel---The-Basics). Today we are going to look at a formula that can help you transfer your data, the VLookup.

The VLookup is one of my favorite functions in excel. It allows you to find values in one table and populate another table with that data.

First, what are the tables? To put it very simply for the Vlookup a table is any group of data organized in columns. Let’s say we own a small boutique that sells women’s shirts. Our first table may be a listing of products we sell. It might look like this:

Here we have three columns of data. The brand, the shirt design, and the color. In a real-life scenario, you would have more columns and rows of data. We will keep it simple and only show three.

The column for Price is empty because that data is coming from a different table. That table looks like this:

In this second table, we have the prices categorized by the shirt design. These two tables may be in the same Excel Workbook (file) in different tabs, or they could be in two different workbooks. In our example, the second set of data is on sheet 2 of the same workbook.

Now we can use the Vlookup formula to connect the price data from the second table to the first table.

The Vlookup Formula is:

=Vlookup(lookup_value, table_array, col_index_num, {range-lookup})

At first glance, this formula looks a little intimidating, but I’m going to break it down for you step-by-step.

Let’s start from the beginning

=Vlookup : When you start with “=VLookup” you are simply telling Excel you are doing a formula for Vlookup. Vlookup means a vertical lookup. Excel will be using the columns as the way to find information.

(Excel has another formula called Hlookup and that is a horizontal look-up. When an Hlookup is used, the rows would be used to find the data. )

The Vlookup formula will tell Excel to look in a certain column and retrieve data.

Lookup_Value : once you have opened the parenthesis the first value you will populate is called the “lookup_value” this value will tell Excel what you are trying look for.

In our example, we want the price populated in table 1, based on the shirt design field. In table one shirt design is in column B.

Our formula would start like this: =Vlookup(B2,…)

This formula now is saying to Excel “we are going to look for a value that matches what is shown in cell B2.

Table_Array is the next field to fill in. “Table_array” means what cells contain the data we are looking for. To populate this field, you will select all the data that is in table two. It is important that the field you are looking to find, Shirt Design, is the first column in the data you highlight under Table Array. Excel will search the first column to find the field that matches the Lookup_Value.

Our Formula now becomes “=vlookup(b2,sheet2!B2:C6…)”

This formula now says to Excel “we are going to look for a value that matches what is shown in B2 (shirt design), we are going to look for the shirt design shown in B2 on sheet two of this workbook in the table from B2:C6.”

Col_index_num is the next field and this one is pretty straight forward. This command is asking when we finding the match for Shirt Design what column should we pull data back for. This will be a number. In our example, we want to pull in the price, which is column 2 of the table.

The formula is now: “=vlookup(b2,sheet2!B2:C6,2…)”

By adding the 2, we are telling Excel to bring back values that are in the second column.

Range_Lookup is either true or false response. True means that Excel will pull back an approximate match and false means it will pull back an exact match.

Typically I use the false because I’m looking for an exact match in data.

Here is the final formula: “=vlookup(B2,sheet2!b2:c6,2,false)”

The statement now is saying to Excel “look in cell B2 and go to the table on sheet two starting in cell B2 look for the cell that matches our lookup value, once you find that match grab the data in column 2 and make sure the match is exact!”

Here is what it looks like as you enter the full formula in:

After you have completed the formula in the first cell, you can pull the formula down to all the remaining rows. Before you pull the formula down, make sure to put the $ signs around the values of the table ($B$1:$C$7). This will keep the table_array range the same so that each row can access the same table data.

Once you learn this formula, you will find it makes life a lot easier. I use this often when I have data from multiple sources. I also will create lookup tables with data when I need to do something like map sales dates to quarters in the year. Give it a try and see if it helps make your data more meaningful to you!

See you Next week!

**We hope you enjoyed our blog. Please note that the intent of this blog is to provide general information and should not be construed as financial, financial tax, accounting, legal, consulting or any other type of advice regarding any specific facts and circumstances, nor should they be construed as advertisements for financial services.