Functions: VLOOKUP

Hello. I am starting the new year with a post on VLOOKUP function. VLOOKUP is one of the most used Excel 'lookup & reference' functions in my experience. Though many of my friends and colleagues are having difficulty in understanding how to use this function, it's fairly simple once you understand the syntax and dirty your hands with couple of practical examples. Let's dive into VLOOKUP.

Introduction

VLOOKUP searches for a lookup value in first column of a selected array and returns a value in the same row of another column. V stands for vertical and it's fairly understood as this function works on columns. Function syntax is below.

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

lookup_value: Is the value you are trying to search for.

table_array: Is the set of columns where you are searching the lookup_value in and want to retrieve corresponding value. In this array, lookup_value will always be searched in the first column.

col_index_num: Is the column number in table_array starting with first column of the array as 1 and counting towards right. This must always be a number equal to or more than 1. If you do not provide a number or provide a number less than 1 or provide a number which is greater than the number of columns in table_array, VLOOKUP results into an error.

range_lookup: This is optional. Is a logical value that specifies if you want to look for an exact match of lookup_value or an approximate match. Since this is logical value, only options available are either ‘True’ or ‘False’. In Excel language, True = 1 and False = 0. Hence you can use 1 and 0 also for this option. If you want an exact match, type 0 (zero) or false, else if you are fine with an approximate match then leave this option blank or type 1 (one) or True. Approximate match in this case means the next largest value that is less than the lookup_value. Point to note here is that if you want an approximate match, your first column must be sorted in ascending order just like in LOOKUP function.

Points to remember:

1. Make sure that lookup_value and table_array values contain the same formatting to the cells. I mean to say if lookup_value is stored as text and table_array is stored as numbers, though the value is available in table_array, VLOOKUP may give an incorrect result.
2. Also extra spaces or line break characters will not be visible to the naked eye but VLOOKUP sees it. So seemingly same cell values may not be same always.
3. You can use wild characters for lookup value. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.
4. You cant't have lookup_value appearing 2 or more times in the first column of table_array. Only the first instance will be considered by standard VLOOKUP. You will have to tweak the original function to work in these cases.
5. Table_array left most column should contain the data you are looking for.
6. Use absolute references in the functions, this is handy when you drag down the function to other cells.
7. If you use 1 or True or omit the last argument (to get approximate match), you MUST sort first column of Table_array in ascending order.

Example: Let's see an example to clearly understand how to use this function. For simplicity sake, I have my data in one workbook. So I have a price list data base for different products. What I want is that when I create a bill with some products, it's price should be auto picked. Let's see how we do it using VLOOKUP.

Price List:

Bill: I need price for product A to F. Here are the formulas I use under the price column.

Let's analyse the formula. For product A, I used VLOOKUP to lookup value in D3 which is "A". Lookup to be performed in Array A3 till B17 that is the place where my price list is available. I need the corresponding result from column 2 of the selected array which is where the price is entered. And since I need exact match for the product, as a last argument, I entered 0 (zero, remember you can also enter FALSE in place of 0). Excel did good job in finding the price 100. I checked this formula result and was happy. To fill the balance product prices I just dragged down the formula in below cells.

Now notice the formula for product B, second argument array is now A4:B18 instead of A3:B17 and so on for other products. Since my price list is in the same order as bill, Excel will still be able to return the correct result but most of the times your data wont be arranged this way and VLOOKUP returns error. Above formula is called relative reference whereas what you need is an absolute reference formula to keep the table array unchanged when you drag the formula below. To use absolute reference, after entering the VLOOKUP formula in first cell (i.e. product A in our example), edit (press F2) the formula. Select A3:B17 and press F4. Doing this places $ (dollar) symbol in front of each row and column reference in the formula. This tells Excel not to change the reference when the formula dragged down below. Updated formula looks like below. Now drag the formula to other cells and notice for product B and so on.

This is an example if you are looking for an exact match. But sometimes you may not want an exact match but an approximate match. One of such cases is while calculating tax. Let's see how VLOOKUP works in this case.

So I have tax rates in a workbook like below. For simplicity I used current year tax rates.

For an income of 2 lakhs we don't have any tax. For income crossing 5 Lakhs, we have minimum 10% of 3 Lakhs which is 30,000 plus 20% of income more than 5 Lakhs. So in above picture, minimum tax is the tax calculated if the income has crossed the limit on left side.

Once we make this table ready, calculating tax for any income using VLOOKUP is easy. I have income of my client in cell D1. If I just want to find out which tax bracket my client falls in, I use the below formula.

=VLOOKUP(D1,$A$2:$C$6,3,1)

I took a random income 9,38,938 and the formula show a result 20%. Let's see the function syntax. Lookup value is in D1, I have to look to find in A2 to C6 and used absolute references. I want to return value from 3rd column in array which contains the tax rate and last argument is 1 to say I want an approximate match (remember this argument is optional, you can use TRUE or simply leave out this argument in this case). Though my random income is not available in tax table, VLOOKUP was able to retrieve the correct tax rate for me. In fact if I am looking only to retrieve the tax rate, I don't need the middle column in my tax table. Then why did I add that? My intention is to calculate exact tax payable for that income. To do that, my formula should be something like below.

=VLOOKUP(D1,$A$2:$C$6,2,1)+VLOOKUP(D1,$A$2:$C$6,3,1)*(D1-VLOOKUP(D1,$A$2:$C$6,1,1))

That formula is a bit long but it gives tax payable as 1,17,788 which I checked back and found correct. Let's also see what we are doing in the above formula.

First VLOOKUP looks up for my random income number is tax table and retrieves the applicable minimum tax rate which is second column in the table. We have used 1 as last argument so approximate value will be searched. Result is 30,000. Next VLOOKUP looks for applicable tax rate from 3rd column and result is 20%. Last VLOOKUP finds applicable income bracket from first bracket which is 500000. Once all the VLOOKUPs did their job, it's simple mathematics done by operators placed in between the VLOOKUPs. I leave it to you to figure out the rest.

I have tried the same formula with different income figures as in picture below. Income figures are in column F.

That's it. You can look and find thousands of values in a fraction of seconds using VLOOKUP. However this function suffers from drawbacks. One is that you cannot return a value which is in the left side of array (LOOKUP function supports this). In the coming posts we will see how to tweak VLOOKUP function to work in that case and get more out of it. See you!!