In this Excel Tutorial I will cover the basics of using Vlookup function using Excel 2010 which will be exactly the same when we will use Excel 2007 or 2013. During this Excel Tutorial I will use Daily Sales Report Template.
Vlookup function is used to find input value in a table and return related information from a different column.
Let's start with an example. In my Daily Sales Report I have three tabs :
1. Sales Data
2. Sales Report
In Sales Data Sheet I have two columns: Product number and Description. Product number is our KEY and description is something we can lookup from the inventory sheet as it will depend on the Product Number (key).
I delete existing formula from Description column and in Product column you can see we can select a product number that we will use to test our formula.
The Inventory sheet shows us all product numbers and the description of it. For example Product number 90001 contain the Description of Blanket. I will copy first value blanket and go back to sales data sheet.
First I will paste Blanket against 90001 product number (see below). In my case I manually searched for 90001 in Inventory sheet which contains description of Blanket and pasted it as a result in Sales Data sheet in description field.
The problem with this approach is that it is very time consuming and if the Description is updated it wouldn't be reflected in my sales data sheet.
This is where Vlookup can help us. Vlookup can take a value (key) like a product number and it can go to another table for example Inventory table and it can perform a search on first (key) column and return result from the column we specify in the formula.
For example for product number 90001 where we want to return second column it would return value blanket.
I will show another example of how Vlookup functions. I copy the 90005 value in Product number column and I go to Inventory sheet.
In Inventory tab I will select the entire product number column (Key column) and perform a search for this value.
Next I go one column to the right side (second column) and copy Round Plate value and come back to the Sale Data sheet.
I go back to my value 90005 and paste Round Plate in the description.
This is what Vlookup does - it will take the value, it will lookup against the table and it will return value from the column we specify.
Now I will show some example of actual Vlookup formula.
First I will type in cell below Description column =Vlookup and open parenthesis. Vlookup function takes several arguments. First is the value I want to find (key). In this case I will select Product number.
The second argument is the table we want to use to find the value. To do this I will go to Inventory. In this case I cannot click my navigation at the top.
So I will go back to second argument and click the tab at the bottom inventory sheet.
Now I can select my range. I will use shortcut to select everything. The result is the table inventory.
Next - column index number indicate which column we want return. The important thing to know is that the first column must be the column that contains key value we want to find. I have two column in my table. If I select column one it would return exactly the same value (if it can find it) but if I put column two it will return the value from the second column. For example if we have number 90010 second column is Fork,Large which is the result I would want to get.
The last argument is range Lookup where we have True - Approximate match and False- exact match. In most cases we want to have the second argument False - exact match. That means that a search is performed using exact match as opposed to approximate match.
I select FALSE (Exact Match - you can also use 0) and close the parenthesis.
The formula was populated automatically for all cells. You can see that for example 90001 product number - inventory table contains to description of Blanket. The rest of product number also contain correct description.
Now I will show you how you can manipulate the data and what kind of effect is has on our formula. I will change the product number to the next one.
You can see that Description changes from Blanket to Pillow. If we change it to previous number it will change back to Blanket.
Description is connected to the Product Number which gets the description from the Inventory sheet.
So what will happen if I update description in Inventory tab? Let's check it. I change Blanket to Blanket, Large and our Vlookup will get refreshed (sometimes depends on settings) and we get updated description.
I hope this Excel Tutorial was helpful.
Katie & Emil