In this tutorial we will learn how to use VLOOKUP formula in Excel to get things done effectively.
In case, If you don’t know what is VLOOKUP then no worries….you are in a right place. I’ll start with basics and then gradually walk you through all real-world use cases.
Lets kickstart this Excel VLOOKUP tutorial and get most out of this.
Why should you learn Vlookup?
Trust me, Vlookup is not just a formula but it’s a kind of skill to crunch numbers or to speed up your data analysis process.
In fact, “Do you know VLOOKUP” was one of the questions that I have been asked in my first interview.
Personally I use almost everyday and I’m confident after reading this tutorial you will use as well.
What is Vlookup?
In simple words, VLOOKUP is an excel formula used to extract values from one workbook/sheet to another based on a common value.
For example, if you have Product ID and Product name in one workbook and Product ID and Sales in another workbook. You can use Product ID as a common value and pull the product name.
Too much of theory I guess…lets quickly move on to the next section i.e. the VLOOKUP syntax.
The syntax
There are four parameters in VLOOKUP formula:
lookup_value: It’s a common value between two different excel files or sheets
table_array: the data set from where you want to extract information
col_index_num: the column number from which you would like to extract data
[range_lookup]: define whether you want to find exact values or approximate
The Basic Excel Vlookup tutorial
Example #1
To have a strong foundation, I have created a very simple VLOOKUP exercise here. As you can see from the below screenshot.
We have Sales & Country Code in one table and in another table we have both Country code as well as Country Name. So, we want to add the Country Name in the first table using country code as a base.
Step 1: In Row ‘C2’ start adding VLOOKUP formula and select Lookup value i.e. ‘B2’ in this case
Step 2: The next parameter is table array, in this case, the entire column ‘F’ & ‘G’, note it’s not necessary to select the entire column, you can also specify range i.e. ‘F1:G7 ‘ in this example
Step 3: Now you need to add a column index number. In simple terms, the count of columns starting from your lookup value. In this case, Colum ‘F’ is your lookup value and from there to ‘Country Name’ i.e. 2
Step 4: Now the last step is to select range lookup. In most cases, you would want to choose ‘FALSE’ i.e. ‘Exact matching’
Step 5: Drag and drop formula to the next cells like below to fill country names
EXAMPLE #2
Here is another example where the base data is on a different sheet. But as you can see below from the screenshot, we need to fill in the sales rep’s name based on his/her ‘Emp ID’.
The procedure for this exercise remains the same as above but the only difference is the table array selection from a different sheet.
As you can see below, the table array is being extracted from ‘Data’ sheet range column C:D
Advanced Excel Vlookup tutorial:
Now the stage is set with the above basic VLOOKUP tutorials, let’s move up a bit in the ladder. Like any other formulas in excel, we can also miniplate VLOOKUP with various combinations.
Example #3
For this exercise, I have used the same data as above Example#1 but the only difference is that now we have two independent data tables for our reference.
As a result, in case if there is no lookup value found in the table that we are looking then the VLOOKUP formula will return the #N/A error like below.
In this case, the country code that we are looking for is in the second table. But how do we get that data in our primary table is the question that we will have to answer.
For this problem, we can use the IFERROR excel formula to set up a condition and add one more VLOOKUP to extract data from the second table.
Add first VLOOKUP formula by following Step 1 to Step 5 as above and follow next steps as per below:
Step 6: Once you have the first VLOOKUP formula in place, just add the IFERROR formula in the beginning
Step 7: IFERROR has two parameters:
1) value: you can add any value here, in this case, the value from 1st VLOOKUP
2) value_if_error: here we define a condition i.e. if 1st VLOOKUP returns any error do this
Step 8: So your 1st VLOOKUP is value here in this case and then add coma
Step 9: Now add the second VLOOKUP but this time the table array should be Column H:I and then close the bracket
Here is a result, basically the first VLOOKUP will check data in first table and if there is any error then it will activate second VLOOKUP to extract the data.
You can use this for many purposes, sometimes you will have to look up data from multiple workbooks in that case you can just use this formula to get data quickly.
I personally use this method for many calculations in my daily work.
Vlookup tutorial for calculations
VLOOKUP is not just limited to data mapping but with little manipulations, we can also perform calculations for our convenience.
In this section of the tutorial lets learn how to use VLOOKUP for quick calculations
EXAMPLE #4
In this example, I have two tables in my workbook, the first table has products and units sold data and in my second table we have products and price/unit data.
Using these two tables we need to calculate and fill the sales column in the first table. The basic math for this operation is units sold * price/unit.
As you can see from the below screenshot, I have first referenced column ‘B’ and then I did a simple VLOOKUP to retrieve Price/Unit based on product name.
And with a simple multiplication our problem is solved.
Of course, this is not the only way, you could also add the Price column in the first table and fill data with a simple VLOOKUP and then multiply in a different column.
But as you can see this method is quick and saves lot of time compared to the other method.
Also read: 51 best excel tips & tricks
5 Vlookup LIMITATIONS that you should be aware of
So far in this VLOOKUP tutorial, we have learned how to do VLOOKUP in excel, but at this stage, it’s also very important to understand the issues that you might encounter while using this formula.
1) Only Left to Right lookup
It is a very well-known fact that VLOOKUP can only perform lookups from left to right, but not the other way around.
There are many experts who argue that VLOOKUP can be used to lookup values from right to left by combining additional formulas.
In my opinion, combining various formulas is a bit tricky game of choice and it could be difficult for a common user, hence I would still call it a drawback.
Possible solution: Use MATCH& INDEX formula
2) Works well with only unique values
Vlookup generally considers only the first lookup value and ignores all the other lines with the same lookup value.
If you have a lot of repetative values in your data, then without a workaround you can’t really use the VLOOKUP formula to retrieve information.
The worst part is you will not see any errors to highlight this issue, as a result your data will be understated.
Possible solution: Insert a pivot table to identify unique values and then you can use the VLOOKUP function
3) Static index number
Since we enter the column index number manually in the VLOOKUP formula, you will find it difficult when you drag/copy the same formula to different cells.
Possible solution: You could possibly use dynamic index numbers with Match function
4) Default ‘approximate match’ setting
If you notice closely, match criteria in the VLOOKUP formula is an optional feature (it will be enclosed will square brackets []), so even if you don’t enter your specific match type, the formula will work based on the default ‘approximate match’ setting.
You must pay attention to match type otherwise you will most likely end up with incorrect results.
Possible solution: Make sure to always use ‘Exact match type’ to get better results, but having said that, if you still need to use ‘Approximate match’ then my suggestion would be to handle it with care.
5) workbook performance will take a hit
There was an argument on VLOOKUP regarding, excessive use of VLOOKUP in your workbooks might reduce performance and sometimes lead to a crash.
I think it’s not really a formula limitation, but it is something related to user specific.
I’d recommend you to read our article on excel reporting tips where I have disused most common excel problems & how to deal with them
Possible solution: You can use any number of VLOOKUP’s in your workbook, but make sure to use ‘paste special‘ future to get rid of unwanted formulas and keep only values for better performance.
Vlookup not working – Top 5 reasons
There are many reasons why the VLOOKUP formula will not work even though you fulfilled all the parameters. I think it’s important to have a basic understanding of what’s happening behind the formula.
#1 Formatting issues
This is the most common reason for VLOOKUP not working the way you wanted. Given that we extract data from ERP systems, sometimes your data stored in TXT format.
Although it looks alright from our views but when you place your courser on a specific cell you will see the actual format.
So when you have two different formats your formula will not find them unique hence start throwing errors.
Solution: make sure your data is in the same format before applying VLOOKUP, if not you just need to quickly convert them using formatting options
#2 Blank/unwanted spaces
Sometimes your data may have one or two blank spaces hence this will cause a lot of issues as you can’t really see the blank spaces.
Again this will result in a mismatch between your base data and the other data that you are trying to bring in.
Solution: You can easily remove unwanted spaces using the TRIM formula
#3 Special characters
I have personally seen many times that the data comes with special characters like a single open quote like this ‘
For example, if you have sales values in your workbook the value of sales would have been shown like this ‘17000 [started with a singlequote]
This will happen due to TXT/CSV to excel conversion in most of the ERP systems or online tools.
But the good news is Excel will automatically identify these and will show you a cell error symbol [also green cross on the top left] so when you click on it you will see issues related to that.
Solution: Just click on a cell and from the error symbol dropdown and click on convert to number
#4 Different workbook types
In some cases when you are doing VLOOKUP between two different workbook types you may see few errors .
For Example – VLOOKUP between XLSB file with XLSX will result in issues due to number of rows and columns.
Solution: Make sure you have both the workbooks are in the same workbook file type i.e. XLSB vs XLSB
#5 Mannual errors related to column index numbers
I have already highlighted issues related to this but let me reiterate. Given that we have will have to enter these numbers manually there are chances that you might enter incorrect reference points.
As a result the entire lookup reference will get impacted.
Solution: Simple solution is attention to detail
Conclusion:
Like any other formula VLOOKUP too has some issues but when you compare to its use cases I guess issues are manageable.
Given that it’s very simple and easy to understand this formula has gained so much popularity.
Ok, now we are at the end of the Excel VLOOKUP tutorial, I hope you enjoyed this tutorial, let me know your feedback, and do not forget to share your tips in the comments section.
Also read: 10 reasons for excel formulas not working