VLOOKUP is another helpful feature to find that needle in a haystack, or when you don’t have time to type in the related data. It’s a way to bring together two sets of data based on one common item.
Grasp the potential of VLOOKUP
As you are given more and more data to look at, you’ll have different spreadsheets which contain similar data, and you’ll need to match them together.
For example, you could export data from a data management system on one day, with a list of clients and products. Then, a month goes by, 🕰 and you export data once again.
You want to compare which clients bought something in both months. How could you compare the two client lists quickly to see who purchased items, two months apart?
VLOOKUP can help you match data from one spreadsheet and join it with a chosen main data set.
Manually looking at the data can lead to errors and be time-consuming. You'll also need to type or copy and paste it which takes time as well.
VLOOKUP is a function to lookup data in one column in your main data set and give you the related data from another tab or spreadsheet. The data will show up in another column (often the adjacent one) in your main data set.
Write a VLOOKUP function
Before we take a look of the function in action, let’s breakdown the parts to the formula. VLOOKUP uses four arguments, or pieces of data:
The first argument is called the
search_key, and it is the data you know. This could be A1 for example.The next argument is the block of values that you want to search: the
range.You can use any cell range that you think will return the data you need to find, but bear in mind one rule: the data you are looking for must be on the right-hand side of the range you've chosen to search in. You can have data to the left of your range, but VLOOKUP won't search there.
The next argument is a
column index. It tells VLOOKUP where you expect to find the data you want to see, i.e. the column where the returned value is supposed to be found. This number of the column index is the number of the column in the range where the first column is numbered 1.For example, if you use column B as a range, and you know the data you're looking for is in column E, you need to enter
4. In this scenario, C is column 2, and D is 3.
The last argument tells the function whether you want an exact match or a partial match to your lookup value. You need to specify either TRUE or FALSE. More often than not, you will need exact matches, so you'll use FALSE.
Put together, this is what this looks like:
=VLOOKUP (search_key,'Name of sheet'! Range, column index, FALSE).
In action, the above could translate into the following:
=VLOOKUP(B6, 'Sales Managers List'!B6:C10, 2, FALSE)
Here is a quick demo:
Let’s recap!
VLOOKUP is a way to bring together two sets of data based on one common item.
VLOOKUP uses four arguments, or pieces of data:
=VLOOKUP (search_key,'Name of sheet'! Range, column index, FALSE)
You can use any cell range, but the data you are looking for needs to be on the right-hand side of the range you've chosen to search in.
A tricky part of VLOOKUP is the column index. It tells VLOOKUP the column where the value to be returned is supposed to be found. This number of the column index is the number of the column in the range where the first column in range is numbered 1.
Now you know about a few functions, let's take a step back and look at something a little simpler which can reduce errors in your data analysis. You need to make sure data is valid so that formulas and functions produce the results you expect. Let’s take some time in the next chapter to work on data validation within a spreadsheet.
