In this tutorial you will learn:
- When to use a vlookup.
- How to use a vlookup.
- Some watch-outs with vlookups.
When to use a vlookup
- You have long lists of data where you need to go between stuff
- 2 sheets where you need to match data on one sheet on to the other
Basically a vlookup is an excel function that allows you to match 2 sets of data together as long as there is 1 thing in common between them to match.
How to Use a vlookup
Here’s the sample data so that you can follow along.
There are 2 sheets of data that we’re going to use to match.
The first is ‘Part Information.’ This sheet is where we are going to practice using a vlookup. The first column is ‘Part Number’ which is a unique identifier for each part. ‘Part Name’ is next. Finally there is ‘Inventory’. I filled in the first cell with a vlookup to pull the part inventory quantity from the next sheet in the demonstration. Now, if you didn’t want to use a vlookup, you could have hand keyed each inventory from the list on the other sheet, but that isn’t very efficient for longer lists, which is when you would want to use a vlookup.
The second sheet is ‘Part Inventory.’ This is where we have our inventory data. Just like the previous sheet there is a column with the ‘Part Number,’ except that some of the numbers in this list don’t match the ones in the other list. They also aren’t in order. Next to the ‘Part Number’ is the quantity in inventory.
As you can see, the common piece of data between both data sets is the Part Number. So in this demo, I’m going to use the part number from ‘Part Information’ to search ‘Part Inventory’ for the matching. For the Widgets, if you manually match them together you can see in the ‘Part Inventory’ sheet there are 75, and that is the value that the vlookup found and put in the cell.
Now that you’ve seen the result of the vlookup, I’ll break down how it works and how to use the formula.
There are 4 parts to a vlookup(lookup value,table array,column index number,range lookup)
lookup value: This is the number that you are trying to find a match of.
table array: This is the selection of the other table that you want to get data from. The value that you are matching must be in the first column (the one on the furthest left of the range that you select). You must include a range with the match and the data you want to pull over.
column index number: This the number of columns to the right of your lookup value. The number starts at 1.
range lookup: This will be a true or false. A true means that it will find the largest number that is less than your lookup value. False means that it will only find exact matches. I generally use false.
Here are the parts in action. The first step is to type =vlookup( in C3 on ‘Part Information’. Next you’re going to select A3 in the first position.
Next, select A2:B6 on ‘Part Inventory’. The first column (A) in this range includes the numbers that A3 is supposed to find the match in as well as the quantity of part number 1 which I want to show up on ‘Part Information’. The inventory quantity is in the 2nd column of range A2:B6, which is why I put a 2 in the next position. Finally, I put a false, because I want an exact match.
So my formula is filled out, and now it is going to search the first column (A2:A6) with in A2:B6 on ‘Part Inventory’ for the number 1. It found number 1 in A5 on Part Inventory.
Next, it is going to use the 3rd parameter, which is 2 in this case, to find the 2nd column in A2:B6 which is column B.
Some Watch Outs and Tips to Using Vlookups
You can use vlookups on text as well as numbers. This demo used numbers exclusively but you can vlookup anything in a cell.
You can do a vlookup of 2 column of text (such as a first name and last name), by concatenating the cells together in your formula like this: vlookup(A1&” “&B1,range,#,false).
If there are multiple matches in the table array, your vlookup will only find the first one from the top.