The most powerful formula that every Excel user should know
Formula complexity: Medium
When I saw VLOOKUP in action for the first time, it was magic. Hours of work turned into seconds! In this post, I’ll show you how to use VLOOKUP in Excel step by step.
Download this workbook to follow along:
Why would I use VLOOKUP
VLOOKUP can be used in a number of scenarios. A common scenario involves appending information to a primary list that has to be sourced from a secondary lookup list.
Let’s say I have a list of students from my class with their Student ID and name, but I don’t have their phone numbers because they’re in a different list.
In order to get their phone number, I need to reference the lookup list that has the phone numbers of students from the entire school.
Obviously, I could manually search the lookup list for each Student ID in my primary list, but that would take ages.
The best way to get their phone numbers into my primary list is to use VLOOKUP. Let’s see how…
What is VLOOKUP
Before we dive into creating the formula, let’s think about how we would complete this task manually.
- We would search the rows in our lookup list until we found the student ID that we’re looking for.
- Once we found the row that has the right student ID, we’d grab the phone number in that row and bring it back to our primary list.
VLOOKUP can do what we just described manually and calculate it across tens or thousands or rows in seconds.
Now that we know what we can do with VLOOKUP, let’s use the function arguments window to learn this new formula.
Using the function arguments window
- Select the empty cell that you want to pull the first phone number back into. In this case, cell D3.
- Click on the fx button to the left of the formula bar.
- Type ‘VLOOKUP’ into the search field and click on the ‘Go’ button.
- Select ‘VLOOKUP’ from the function list and click ‘OK’.
- The Function Arguments window will open and this is where we start to understand the formula.
Below the input cells in the Function Arguments window is a description for each argument that helps us understand what we’re inputting.
Adding the arguments
- Lookup_value: is the value from our primary list that we’ll try to find in our secondary list, which should be the common value between both lists.
- Our input: B3 – the Student ID value of the first student in our primary list.
- Table_array: is a table of information in which data can be retrieved. The word ‘array’ can be intimidating, but it simply means ‘a range of cells’.
- Our input: $G$2:$H$42 – the range reference should be absolute to ensure that when you copy the formula down, the range does not change.
- Col_index_num: is the column number from your table array that contains the data that you want to pull back. The first column from your table array is column 1. So if our table array is $G$2:$H$42, column G represents column 1 and column H represents column 2.
- Our input: 2 – because we want to return the phone number from column H.
- Range_lookup: allows us to control if we want to find an exact match to the value that we’re looking for, or if we’re looking for the closest match. You’ll usually use FALSE here to ensure only an exact match is used. TRUE is a bit more complicated.
- Our input: FALSE
The list now has the first student’s phone number and we can use the formula that we just created to populate the rest of the missing phone numbers.
Drag the formula down to the rest of the rows with the auto fill handle and voila!!, you’ve got all of the student’s phone numbers next to their names.
Other awesome ways to use VLOOKUP
If you’ve made it all the way to the end of this tutorial, congratulations! You just learned how to use VLOOKUP in Excel, a formula that most everyday users don’t know, and it’s costing them hours.
There are a ton of really cool ways to use Excel, so keep learning and find new ways to use it in your everyday role.