Web Tutorial Plus

VLOOKUP is a popular function in Excel which helps you to retrieve the desired information from a data table. If you are working in some area where you need to do a lot of data handling in spreadsheet, Excel VLOOKUP can do wonders for you. Once you learn to use VLOOKUP in Excel correctly, you will save a lot of your time and energy.

VLOOKUP in Excel is really interesting and useful, but a bit confusing. In this VLOOKUP tutorial, I will use day-to-day examples so that it becomes really easy for you to understand it once for all!

This vlookup tutorial is meant for those who have at least basic knowledge of Microsoft excel and can do basic operations like SUM, AVERAGE, etc. Of course, once you learn vlookup, you can start considering yourself as an advanced excel user!

VLOOKUP in Excel

Primary and basic function of VLOOKUP is to search any value from a table with data and return a value from a different column from the same row. A typical use of VLOOKUP is to match two data tables based on a common field. Little confused? It will be pretty clear with the following vlookup example.

Say you have a simple data table in excel with two columns which contains a list of your friends and their email ids. Now, if you are looking for the email id of one of your friends, what will you do? If the list is short, you will probably look manually, find out the name from the left column and see the corresponding email id in adjacent column. Very simple….Good!

Now let’s assume that you are very social and as such the number of your friends in this list is 1000, then what? I am sure, you will like to use ‘find (ctrl+F)’ function to search your friend’s name. Excellent….!

Here comes the interesting one…! You are going to celebrate your birthday and want to invite only close friends. You have listed one hundred of them and want to send them invitations. Now what? If you don’t know about excel VLOOKUP function, you are going to take at least some minutes collecting their email ids. And if you want to send a hard copy of invitation and print the address lines as well from a different data table, it may take hours. Worse, it is really a boring and repetitive work.

Here comes VLOOKUP for your rescue. Within one minute you will have the list ready! Interesting?

VLOOKUP Formula Basics

Let’s get into the basics first. We will use the same simple excel table here with names and email ids.

Let’s use VLOOKUP to retrieve the email id of Amit. Write ‘Amit’ in col C, row 1 (or simply C1). We will retrieve the email id in a blank cell (say D1). Just follow these steps:

• Select the blank cell (D1) where you want the email id
• Click insert function icon

• Select category ‘Lookup and Reference’ and function ‘VLOOKUP’

• Click ‘OK’ and you will be presented with ‘Function Argument’ pop-up box

• In Lookup_Value, select C1 (i.e. Amit)
• In Table_Array, select A1:B9 (entire table)
• In Col_Index_num, put 2 (the column number from left where your lookup value lies, in this case it is column B, hence number 2)
• In Range_lookup, write ‘False’ (More on this later)
• Click ‘OK’ and you get the email id of Amit

Pretty simple till now….Great! We will have a look at it in detail.

Explanation of VLOOKUP function

Lookup_value

The value or reference you want to search. This value must be from the first column of the selected table array. It does not necessarily mean that you have to place the Lookup_value in first column of excel sheet. But while making the selection of table array, you should always select it as first column.

As you may see, our table_array is from A1 to B9 and the value to search for is in first column i.e. A.

In our example, we have used a reference (C1) as a Lookup_value. Instead of using reference, we can also use value (like Amit) to get the same result. However, in real world, you will be using the references most of the time.

Table_array

Table_array simply means the data table from which you want to pull out the information. The data table may contain one or more columns of data. Just make sure that the value you are going to search is placed in the first column of this array.

Col_index_num

Now, you have a value to search for from a specific data table. After searching this value from the first column, which information you want to retrieve? Here in our example, we are interested to retrieve the email id, which is placed in the second column of the data table. So we are putting the column index number (Col_index_num) to 2.

Can you guess what will we get if we put 1 instead? If your answer is ‘Amit’, you are right. Why? This is because the 1st column of the data table contains the names.

Range_lookup

Range_lookup is an additional feature which lets you specify whether you want to search for an exact match or an approximate match. By default, you should always use ‘FALSE’ as Range_lookup value to search for the exact match. If there is no exact match, the error #N/A is returned.

Here is a small tip – you can put 0 (zero) instead of FALSE to save time.

Note however, that Range_lookup is an optional field. If you don’t specify anything, it is ‘TRUE’ by default. So if you put Range_lookup as ‘TRUE’, ‘1’ or simply omit, what will happen? VLOOKUP will try to find out the exact or approximate match. If exact match is not found in the first column, it will return the next largest value that is less than the lookup_value. Say your lookup value is 100 and the first column contains only 60, 80, 90 and 120. So in this case, VLOOKUP will return 90 as this is the next largest value which is less than 100.

For Range_lookup to work properly, you need to sort the values in the first column in ascending order. If lookup_value is smaller than the smallest value in the first column of table_array, the error #N/A is returned.

If you are finding ‘Range_lookup’ a little confusing, just keep in mind that you will almost always use ‘FALSE’ here. Later in this article, we will discuss little more about Range_lookup with ‘TRUE’ value.

Notes:

1. If lookup value is appearing two or more times in first column of table array, the result of first value from top is returned.
2. There is no difference between uppercase and lowercase text – Amit and amit will return the same result.
3. If you put col_index_num less than 1, error #VALUE! is returned.
4. If you put col_index_num greater than the number of columns in table array, error #REF! is returned.
5. V Look Up – Stands for Vertical look up. Excel also has a similar HLOOKUP function.

VLOOKUP Syntax

Instead of selecting VLOOKUP function from ‘Lookup and Reference’ category, we can simply write theVLOOKUP syntax to retrieve the same result. Not to mention, it will save you a little time! The syntax for VLOOKUP function is as follows:

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

For using syntax, simply type ‘=VLOOKUP(’ in the selected row (in our case D1), and select the desired values followed by comma (,) like this:

=VLOOKUP(C1,A1:B9,2,FALSE)

And at the end, don’t forget to close the bracket.

Pretty simple till now…. Great! Let’s move ahead.

Matching two data tables with VLOOKUP

For now, we will stick to the example of birthday celebration. For understanding purpose, let’s assume you have two data tables in two different sheets of an excel file. First sheet contains serial no, name of your friends and their phone number. In second sheet, you have names with address line 1, address line 2 and address line 3. And you want to send invitation to 3 of your friends (Of course you will invite more friends; we are taking 3 just for sake of understanding) which is in the third sheet. You want to get the addresses and phone numbers of each of these three friends.

Here is how the sheets look like. At the end of this article, I have provided the excel file for you to download and experiment.

First of all let’s get the phone number of Rahul in sheet no 3 (named Invite). We will repeat the steps again here.

• Click ‘insert function’ icon and select ‘B2’ in the Lookup_value (corresponding to Rahul).
• Click inside the Table_array field and then go to sheet no 1 (i.e. Phone).
• Make a selection of Table_array from B2 to C11
• Put Col_index_num to 2 and Range_lookup value to 0 and click OK. Excellent…!

Here, you should notice two things:

1. If you want to select a data table from different sheet, you need to put the sheet name followed by ‘!’ before the table array reference. In this example note – Phone!B2:C11
2. We have selected table array starting from column B to make sure that the Lookup_value is in the leftmost column.

Now for retrieving the address line 1, 2 and 3; we will straightaway use VLOOKUP syntax.

So, in cells D2, E2 and F2, we will write the following syntaxes.

D2 =VLOOKUP(B2,Adress!A2:D11,2,0)
E2 =VLOOKUP(B2,Adress!A2:D11,3,0)
F2 =VLOOKUP(B2,Adress!A2:D11,4,0)

If you have followed correctly till now, you should be pretty comfortable with these syntaxes.

Great… you have got the phone number and address of one of your friends. So do you need to repeat the same steps for other two (or many) also? Absolutely not! We will use cell drag to get the information for your other friends. Select the four cells which contain the phone number and address of Rahul (i.e. C2 to F2) and drag it till row 4.

For those who are not aware, cell drag can help you to do automatic calculation in excel. You can drag any cell by clicking on small + sign at the bottom right corner or any cell and excel will fill the dragged cells automatically. See the picture below:

Let’s get back to VLOOKUP now. You must have observed that although in all other cells we are getting the correct values, cell C3, corresponding to phone no. of Anu, shows error ‘#N/A’.

Here is a small catch. Select C3 and view the VLOOKUP formula in formula bar. What do you notice?

As you do cell drag, the selected table array also gets changed along with lookup value. The table array in case of cell C3 is changed to Phone!B4C13 from original Phone!B2C11. This is because we are using reference here.

To overcome this, we need to put ‘$’ sign before row and cell no which we don’t want to change after cell drag. Hence, the modified syntaxes for cells C2, D2, E2 and F2 will now be as following.

C2 =VLOOKUP(B2,Phone!$B$2:$C$11,2,0)
D2 =VLOOKUP(B2,Adress!$A$2:$D$11,2,0)
E2 =VLOOKUP(B2,Adress!$A$2:$D$11,3,0)
F2 =VLOOKUP(B2,Adress!$A$2:$D$11,4,0)

Now again select these four cells and drag till row no. 4. Excellent…we have got the phone number and address of all friends.

It does not matter now if you want to call 3 or 3000 friends; the time to retrieve the information remains same!

Caution

• If your lookup value in first column of table array is text, make sure that it does not have additional spaces, quotation marks or non printing characters. Otherwise, you may get an error or incorrect result.
• If your lookup values are numbers, make sure that it is not stored as text.

Tip

If your lookup value is text, then you can use the wildcard characters, question mark (?) and asterisk (*), in lookup value. A question mark matches any single character; an asterisk matches any sequence of characters. However, in this case your range lookup must be FALSE.

VLOOKUP with Range_lookup TRUE

As we have already discussed, if you set Range_lookup as TRUE, VLOOKUP will first try to find out the exact match. If exact match is found, it will return the value corresponding to the exact match. However, if exact match is not found, it will search for an approximate match and return a value corresponding to that.

Approximate match for VLOOKUP is the closest value lesser than the lookup value. If there is no value lesser than lookup value in first column of table array, VLOOKUP will return error ‘#N/A’.

For VLOOKUP to work correctly with Range_lookup TRUE, you must first sort the first column of table array in ascending order. Let’s understand it with an example.

Vlookup Example

To boost sales, a shopkeeper gives variable discounts to its customer based on maximum purchase as per the following table.

So, for a purchase between $100 and $200, the customer will get a discount of 2%, between $200 and $300 a discount of 5% and so on. We will help this shopkeeper by creating a form which will accept the total purchase cost and return the actual amount to be paid by customer. This involves the following steps:

1. Calculate the discount to be given based on the discount table (B2:C6)C
2. Calculate the actual discount
3. Subtract discount from purchase cost to get the amount to be paid

For first step, we will use VLOOKUP function with Range_lookup as true. It will search for either the exact or approximate value which is lower than the lookup value. So for a purchase cost of $250, it will return 5%.

The shopkeeper puts the total purchase cost in cell F2, which we will use as lookup reference. The discount percentage will be available in cell F3 and the amount to be paid will appear in cell F4.

So, the syntax for cell F3 will be ‘=VLOOKUP(F2,B2:C6,2,TRUE)’

And for F4 it is total purchase cost – (total purchase cost) X discount %
i.e. ‘=F2 – F2*F3’

That’s all! Experiment by entering random amount between 100 and 500 in cell F2 to get the amount to be paid by customer. Works great, right?

However, if you put a value lesser than 100, it will throw a ‘#N/A’ error. This you already know – If there is no value lesser than lookup value in first column of table array, VLOOKUP will return error ‘#N/A’.

Let’s learn to handle this error!

Error Handling in VLOOKUP

Here comes the last section of VLOOKUP. How to handle ‘#N/A’ error? This can be easily done with ‘IF’ and ‘ISNA’ functions of excel. Excel ‘IF’ is similar to ‘if-else’ in any programming languages. I will write an article for excel ‘IF’ separately in details. ISNA checks if the result is ‘#N/A’ and returns ‘TRUE’ in that case.

The syntax for handling ‘#N/A’ error is

=IF(ISNA(VLOOKUP(F2,B2:C6,2,TRUE)=TRUE), 0, VLOOKUP(F2,B2:C6,2,TRUE))

‘ISNA’ function checks whether output of VLOOKUP is ‘#N/A’ error, if yes, it will return ‘TRUE’.

‘IF’ function in this formula simply checks whether the value of ‘ISNA’ function is TRUE or not? If yes, it returns 0; otherwise the value of VLOOKUP is returned.

Now, replace the syntax in cell F3 to ‘=IF(ISNA(VLOOKUP(F2,B2:C6,2,TRUE)=TRUE), 0, VLOOKUP(F2,B2:C6,2,TRUE))’

Wow, you have just created a small application of discount for shopkeeper.

Final Words

With this, we come to the end of the excel VLOOKUP tutorial. Thanks for following this tutorial till the end. I am sure, you are quite comfortable now with VLOOKUP in excel and can use this function easily. If you have any question or comment, please do write in comments section. You can download the two files used in this example here.

VLOOKUP Function Example

VLOOKUP Example – Discount