Are you in search for Excel Vlookup tutorial from a while? If so, you are in a right place. Yes, in this tutorial I’m going to cover end to end guide on how to use excel Vlookup formula effectively by considering various best practices followed by many industry experts.
Personally, I am a great fan of vlookup formula, and I’ll use it in almost all the cases, but, how about you? Do you also use it very often in your work? If not, I think it’s a good idea to learn now..!!
Still not convened? Ok, no problem, here are the facts from my recent research on how many people use Vlookup formula very often in their daily work life.
What research says about Excel Vlookup formula?
As per my recent research that I had conducted through Linkedin & Quora, about 73% of the responses say that they have been using Vlookup almost all the time, and 17% of them have said they are not using Vlookup formula because of its limitations.
I’ve drilled down even further on 17% responses that clearly says they are not using, and in which 9 out of 11 responses says they’ve been using Match & Index & rest they don’t use Match and Index as well.
Further about 10% of the responses say that they are neither using Vlookup nor using Match & Index, So basically they don’t know how to use any one of the formulas or they wouldn’t have got a chance to explore till now.
Just to add, I still remember, I’ve been asked a question related to Vlookup about 6 years back in my first interview.
So now tell me, don’t you think it’s really important to learn Excel vlookup function to speed up your reporting process? If you think yes, then read further…!!
Excel Vlookup tutorial – initial thoughts or basics
Like I said, this article is going to be an end to end tutorial on how to use vlookup formula effectively, I would like to start with “what is Vlookup?”, so back to basics.
What is Vlookup?
Vlookup is an excel formula used to look up values from Left to right with the help of base value.
According to Lucy Stewart from trackpal ” VLOOKUP is used to look up a value from a different location in your workbook”
Syntax: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
1) lookup_value – I would call it as a base value, you can select any text or numbers under this field (please remember, you need to select a unique value to get better results).
2) table_array – Data table in which lookup has to perform to identify the corresponding value of your base data.
3) col_index_num – Column count from base (lookup) value in a data table
4) [range_lookup] – It’s a match criteria, here you’ll have to select either Approximate match or Exact match (most of the times I would prefer to use “exact match”)
Basic Vlookup tutorial:
I’ve a below table in my workbook, where I’ve country short code in one column & country full name in the other column. In my other table I have only country short codes.
Q: I would like to have a country full name based on country code in Column ‘B’
Step #1 Start with an equal sign (like any other formula ‘=’), and type VLOOKUP in cell ‘C2’
Step #2 next, you need to select your lookup value, in this case select ‘AU’ from cell B2 and add coma
Step #3 Now you need to select your table array, i.e. E: F and add coma
Step #4 Select column index number, here I am looking for a country full name in a table range E: F, so we have country name in ‘F’, hence my index number will be 2
Step #5 Match type – I’m currently looking for exact country name, hence we’ll select ‘FALSE’ i.e. an exact match
If you follow all the above steps properly, your formula should look like this.
Once you have completed all the above steps, in cell ‘C2’ you should see ‘AUSTRALIA’. Since you know all the steps, it’s your turn now to fill data for ‘CN’ &’JP’.
Advanced Vlookup tutorial:
The above example is pretty much basic and it’s mainly for those who still don’t know how to use Vlookup at all.
Now that you have a good idea on how to use Vlookup for simple data, it’s time to look beyond boundaries and explore little more.
In this example, we have the same data in two different tables, so you’ll have to write a vlookup formula that looks in both the tables and retrieve data if it finds a match.
Q: I would like to know Country full name for AU,CN,JP,BE &US, however the country name can be found in any one of the tables.
Step #1 Add first Vlookup formula in cell B2 by following all the above steps (from step 1 to 5), if everything goes fine, then you’ll see #N/A in cell B5, because BE is listed in second table
Step #2 #N/A error is a clear indication to say that the value you are looking is not there in Table 1, so you’ll have to do another lookup to retrieve information from second table.
Step #3 Add an IFERROR formula to the existing Vlookup like below to identify errors and perform the next action.
In this case I’m instructing excel to show zero, instead of errors in my formula cells.
Step #4 Now we’ll use the above formula and replace zero with another look-up to retrieve information from the second table like this.
Once you complete, you can see, all the cells would have been filled with respective country names from two different tables.
Though BE & US are in second table, our vlookup formula was built in such a way that, it will go and pick them from second table.
Vlookup for calculations
We all know that, vlookup does a great job in retrieving data from various sources; but what if I say you can use vlookup to perform calculations!
Not sure how to do that?, no problem, here is another example to help you out..!!
I’ve two tables in my workbook, in which first table contains the product name and number of units sold, and in my second table I’ve the product name and price per unit.
Q: You want to know total revenue earned by first three products (please ignore data for the time being) by multiplying number of units sold with price per unit (It looks simple because we have only 3 products, but what if you have 20 or 50 products?).
Step #1 You’ll have to create a new table, in which you need to list out all the unique products in one column.
Step #2 Now you’ll have to do first vlookup to find out the number of units sold, you can add something like below.
Step #3 After first lookup, you’ll see the total number of units sold in a respective cell, so now you’ll have to add multiplication symbol, i.e. “*” at the end of first vlookup and then add another lookup to find out price per unit.
So the formula should look like this
What will happen?
Basically, you are performing multiplication between two vlookup results, so the result will be 1000 for the above example.
Vlookup for calculations – even more advanced
Don’t you think the above method is very useful? If so, aren’t you excited to learn even more complicated use of vlookup formula.?
Vlookup will work effectively only when you have a unique product list. If you have repeated products in your list, then it’ll consider only first one and ignores all other.
Ok, In my above example we have a list of three unique products sold (date need to be ignored for first example) , but what if we sell same products on daily basis?
We will use the same example (example #3) but with one small change, i.e. we have same products sold on different days. That means your products will be repeated more than once. For example, you might sell Web hosting today, tomorrow and as well as the day after tomorrow.
Here is what you should do:
Step #1 First, you’ll have to find out sum of total units sold by each product, using SUMIF formula like below.
=SUMIF(B3:B8,B13,C3:C8) result will be 15
- B3:B8 is our product range
- B13 is our criteria, for the above formula i.e. ‘Web hosting’
- C3:C8 is our sum range, to find total number of units sold
Step #2 Now that you’ve total numbers of units sold by products irrespective of duplicates, it’s time to identify price per unit using a simple lookup formula like this.
VLOOKUP(B13,E2:F5,2,FALSE) the result will be 100
- Web hosting is your lookup value
- E2:F5 is your data table where you price per unit information
- 2 is my column index number
- We’ll continue to use ‘Exact match type’
Step #3 Now that we know the total number of units sold and price per unit, use simple multiplication in your formula to find out revenue for each product. You can build your formula like this.
=SUMIF(B3:B8,B13,C3:C8) * VLOOKUP(B13,E2:F5,2,FALSE)
5 biggest drawbacks of Vlookup that you should know before you use
As per my initial research about 17% of them clearly stated they are not comfortable with vlookup formula because of its drawbacks, and most of them also commented that the only possible solution to this could be using Match & Index formula.
1) Only Left to Right lookup
It is very well known fact that, vlookup can only perform lookups from left to right, but not from right to left.
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 as a drawback.
Possible solution: Use match & Index formula
2) Works well with only unique values
Vlookup generally considers only first lookup value and ignores all the other lines with the same lookup value. If you don’t have unique lookup values in your data, then without workaround you can’t really use vlookup formula to retrieve certain information.
Possible solution: Insert a pivot table to identify unique values and then you can use vlookup function
3) Static index number
Since we enter the column index number manually in vlookup formula, you will find little difficulty when you drag the same formula to a 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, formula will work based on default ‘approximate match’ setting.
You must pay little attention to match type, otherwise you may have to end up with incorrect results.
Possible solution: Make sure always use ‘exact match type’ to get better results, but having said that, if you still need to use ‘approximate match’ than my suggestion would be “handle with care”.
5) workbook performance will take a hit
There was an argument on vlookup saying, 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 we’ve 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.
Unlike any other formula vlookup is not exempted from its limitations, so it’s time for you to re-think and start making use of the above excel vlookup tutorial to move ahead.
Believe me vlookup is one of the most used formula in the corporate world with lots of dependency for regular work. So its time to get started…!!!
Have you been using Vlookup from a while..? then you must have something to share with us. Please comment your opinions below.