vlookup.co
Learn VLOOKUP HLOOKUP and INDEX MATCH In-depth

What is VLOOKUP?

VLOOKUP is short for Vertical lookup, V stands for Vertical. We have another lookup called HLOOKUP (H stands for Horizontal) used for Horizontal lookup. In Excel, the data is arranged vertically rather than horizontally, and we end up using VLOOKUP more than HLOOKUP.

You can insert VLOOKUP formula from Formulas tab Insert function. But in this guide we are not using it that way, we will type the formula directly in the cell and use. This gives you more control to the VLOOKUP formula and you will also learn thoroughly.

Syntax:

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

When you first see the syntax it is confusing. But if it is properly explained with an example, you will understand very quickly. Vlookup is devided into four parameters within the paranthesis and is separated by three commas. The last parameter range_vlookup is an optional parameter; you can either use TRUE or FALSE as the fourth parameter. If you omit this parameter, by default, it will be TRUE. Instead of TRUE, you can use 1, and for FALSE you can use 0.

03 Vlookup with fourth parameter FALSE

Example: Open the file Chapter 1.1 - Vlookup.xlsx

In this example, we have sales person’s name in A column. D column has three names from A column qualified for the yearly bonus and E column has their bonus amount. Please go through the image below to know how data is arranged in Excel.

(example)

Now using Vlookup, we compare the names mentioned in A column with D column and fetch the bonus amount correspomning to each person if the name matches. Here only Noah, Oscar and William are qualified for bonus. Now we want that amount to be in B column beside the person’s name in A column. Here the data is small; we can find out the bonus amount without using Vlookup. But in the real-world scenario, the data will span thousands of rows and columns, and there you have no other options but to use Vlookup.

Now enter Vlookup formula in B2 cell like this =VLOOKUP (A2 ,$D$2:$E$4 ,2 ,0 ) (have put some space in between the parameters to see it better). There are four parts and what each part is doing is explained below. Just keep in mind there is opening and closing paranthesis, and there are three commas inbetween the parameters.

A2 - lookup_value, this is the first parameter. What are we going to search, it can be a text or number or cell reference having the values.

First, we are searching the name George in the cells D2:D4. Now the name George is in the cell A2, so we have used that as the first parameter. You can use George instead of the cell reference A2, then the name will be hardcoded to the formula, and if you copy down it will always search for the name George only.

$D$2:$E$4 - table_array, this is the second parameter where we are going to seach, it will be a group of cells in same sheet or other sheet or another workbook whis is called table array.

I have selected the range D2:E4 because D2:D4 cells have the qualified person’s name and E2:E4 has the bonus amount. Table arrays first column should have the same data you are comparing. So the name should be the first column in the table array. If D column has some other data, Vlookup will return an error.

Also, I have put a dollar sign before the column and row numbers to lock the cells. Putting dollar sign before the row and column reference is called absolute reference. In other words, the name George will be searched inside the cells D2:E4 only. Else if you copy the Vlookup formula down the cell, reference will change to D3:E5, then to D4:E6 because of the relative referencing of Excel. Because of this, the result will go wrong as there is no data in the D5 or E5. If you still don’t understand why the dollar sign is used, refer to the section Absolute reference after this example.

For putting the dollar sign, you can use the shortcut key F4. Once the second parameter is selected use this key and four dollar signs will be automatically inserted. You have to use F4 only once, and if you accidentally press more that once, your reference will change to mixed and then to absolute. You can use F4 till you see four dollar signs. If you Vlookup between separate workbooks then Excel will automatically insert the dollar sign for you.

2 - col_index_num, this is the third parameter. This is the Column number to extract data from the same sheet or other sheet or workbook we are comparing.

In the second parameter we have specified the array of cells which has two columns D and E, D is the first column and E is the second column. So if you want to get the data from the first column, i.e., sales person name you have to put 1, and for second column bonus amount you have to put 2. Obviously, here we don’t want the name of the person; we want the bonus amount, so we have used 2.

0 - range_lookup, this is the fourth optional parameter and the ddefault value is true.

This defines whether the data should exactly match or approximately match. You can enter False for exact match and True for approximate match. Instead of true you can use 1 and for false you can use 0.

Since we want an exact match, we should put False or 0. Be careful the fourth parameter is an optional one and if you forget to type the fourth parameter it will pick the default value true which is an approximate match. This will give you the wrong result and your entire lookup will go wrong.

Then close the parantheses and click enter. You will get this error #N/A means name not available. You can see the name George is not in D column. Now we will copy the formula from B2 to B11 so Vlookup will individually search each name and give back the result from E column.

Below is the image with the result shown in B column and the formula used in the C column.

[example]

Against each search, you got the result and you can see so many #N/A errors in B column. #N/A means data not available. #N/A will happen for other reasons also and this will be covered later in the later chapters. But here only Noah, Oscar and Jack are there in the D column, so their bonus amount is fetched and put it in B column. Just for your information, if you put the third parameter as 1 you will get the name from the D column.

In the image, you can see the names are searched inside the range D2:E4 only. Only the cell reference of the names are changing, table array or second parameter is not changing because of using the dollar sign to lock the cells.

Also if you use 3 or 4 as the third parameter it will result in #REF error (out of reference), means you are referring to the array which is not there. Here table array has only two columns, so if you give 3 or 4 ( as the fourth parameter you will get the #REF error.

Also, you will get value error if you are not entering the formula correctly because of typing or incomplete.

So keep this in mind when you read this book. Lookup value is the value you want to search, the first parameter. Table array is the second parameter whis is group of columns where you want to search. Third parameter is the index of the column number of the table array (second parameter). And the fourth parameter is the True for the approximate match and False for exact match.

Points to note:

To enter Vlookup formula fast, enter the equal sign and first word of the formula vl and then use the TAB to get the Vlookup formula filled till the first paranthesis.

In the second parameter searching person’s name should be the first column. In the above example if the name is in the E column and F column has the bonus, then you have to select E:F as the second parameter. In other words what you are comparing (here it is sales person’s name), that column be the first in the secong parameter.

05 Advantages of Vlookup

Vlookup can avoid the complicated If next statement. In the above example, if you use IF function it will be lengthy and complicated to write as well as maintain.

05 Limitations of Vlookup

The main limitation of using Vlookup is it cannot look left. In the above example, if the bonus amount is in D column and name is in E column Vlookup won’t work. You cannot put a negative number as the third parameter. In this case, you have to rearrange the column to make the cells person’s name as the first column. But this may not be possible in every scenario. In this case, you have to use Index Match or club Vlookup with another function Choose to do a left Lookup. More about this functions are described in later chapters.

Also, Vlookup is not case sensitive. In other works, Vlookup doesn’t differentiate the words Text and TEXT and TeXt. It is all treated as same. We will be exploring a workaround in chapter 6.

It cannot compare multiple values, but you can have a workaround which we will look in coming chapters.

If there are multiple values, it will fatch the first value from the lot even if there are duplicate values.

Also, if you delete any columns used in the second parameter, then the Vlookup will give wrong results. For example, in the second parameter of the Vlookup you have typed A:E, and in the third parameter, you typed five. Now if you delete B column from the database it will give out of reference error as the referencing column is not there. And if you are searching for any middle columns, deleting the columns in between will give wrong results pointing to the wrong column. The third parameter is static in Vlookup and has to be manually changed if you delete a column in between.

05 Relative and Absolute reference

A reference means a cell (A1) or a range of cells (A1:A8) on the worksheet. Reference tells Excel where to look for the values or data you want to use in the formula.

Relative reference
By default, Excel follows the relative reference. For example, if you are adding cells A2 and B2 using Sum formula in the cell C2, the first formula will look like this SUM(A2:B2). Now if you copy down the formula, cell reference inside the Sum formula will automatically change to SUM(A3:B3) then to SUM(A4:B4).

Refer the image below for you to understand.
[example]

This is called relative reference in Excel and it saves a lot of time while you copy down the formula. But sometimes you don’t want this relative reference mode in Excel. For that, you have to change the reference to Absolute. Absolute is explained in the section below.

Absolute reference
Absolute reference means the cell reference the cell will always refer to the same cell. For that, you have to put a dollar sign before the column name and row number. In the above example if you rewrite the formula to =SUM($A$2:$B$2) and copy down the formula the cell reference will not change relatively. It will always get the sum of A2 and B2 wherever you paste the formula in the sheet. So you can say dollar sign is used to lock the cells.

You can use the shortcut key F4 to insert the dollar sign easily and if you keep on hitting F4 key, the reference will change to mixed and to relative.

Here in the image given below total column always return 73 as answer because we have put a dollar sign before column and row cell references.
[example]

For this formula to work we don’t have to put the dollar sign before column reference because column reference is not changing when copying the formula down, only rows are changing. So you can rewrite the formula like this SUM(A$2:B$2) to lock the second row and the result will be the same.

The point you have to remember is by putting a dollar sign before column reference it will lock the column and if you put the dollar sign before the row number, it will lock the row. If you put the dollar sign before row and column reference, then the entire cell will be locked will be locked and the cell reference won’t change if you copy the formula to any other cell.

That is why in Vlookup we use four dollar signs, so if you copy down the formula, it will still search inside those cell range only.

If you are still confused, enter the value 25 in cell A1. In C1 enter =A$1 for getting the value of A1 cell. Now copy C1 cell down a couple of rows. Since the row is locked with the dollar sign, it always points to the first row when you copy down the rows. Now copy C1 cell to D1 and check the formula bar, now the formula has changed to =B$1. What this means is still the formula is refering to the row but we have not locked the column so the column reference will change if you copy across.

Now clear all the contents except A1 cell and enter the formula =$A1 in C1. Copy the C1 cell to D1 and F1. You still get the value 25 because the dollar sign locks the column. Now copy the cell C1 down the rows and see the row number will change and you will get 0 as answer.

Now clear all the values except A1 and enter =$A$1 in cell C1. Now you copy the value across or below; it will always point to A1 cell. This is called absolute reference and we are using this locking for Vlookup formulas.

05 Usign column name instead of cell reference

Example: Open the file Chapter 1.2 - Vlookup.xlsx

Here we have the data in sheet2 as in the image given below. A column has the name of the students followed by Subject, Month and Score. We will use this as our database of students. [example] For retrieving the data, I have designed a form in sheet1 (refer the image). This way you don’t have to search the data in sheet2 directly to avoid any change in the data by mistake. [exapmle] Now if you enter the name John in cell C2 you should get the Subject, Month and Score below in the cells C4, C5 and C6. First, you type the name John in C2 cell and then you have to enter the Vlookup formula like this. In cell C4 enter the formula =VLOOKUP(C2,Sheet2!A:D,2,0). In cell C4 enter the formula =VLOOKUP(C2,Sheet2!A:D,3,0). In cell C4 enter the formula =VLOOKUP(C2,Sheet2!A:D,4,0). Now you will get the Subject, Month and Score in C4, C5 abd C6 cell. In the second parameter instead of the actual cell reference A2:D9, I have given the entire column name (Sheet2!A:D) without specifying the row number. By using the entire column name whatever new records are added in sheet2 and you enter that name in c2 cess of sheet1, you will get all the details immideately. Giving the entire columns as second parameter is not recommended because the table array will be big and will slow down the Vlookup formula. Instead, you can put an appropriate row number. For example, if you are sure the student's number will not cross thousand, you can change the third parameter to Sheet2!A2:D1000. Look at the third parameter in each Vlookup formula. We have changed the column number to get Subject, Month and Score. For Subject we used the column number 2, for Month we used 3 and for Score, we used 4 to get the data from table array. Depends on the data we want to fetch, the column number has to change.
05 Copying Vlookup to multiple columns

If you want to fetch data from so many columns, then it will become a big headache to write so many Vlookup formulas. Instead, you can write one formula and then copy to other columns and it will save so much time. In this example, we doing that. Example: Open the file Chapter 1.3 - Vlookup.xlsx Here we have a data in sheet2 like this image given below. [example] In sheet1 we have these four Product id's WH955, WH768, WH968, WH551. Now we want Products, Quantity and Rate/Unit of these four Product id's from sheet2 to be in sheet1. For getting these three details, you have to write three Vlookup formulas separately. Instead, you can put a dollar sign before the column name in the lookup value like this $A2 in the first formula and copy the formula across three columns. Dollar sign will lock the lookup value and after copying change only the third parameter to get correct details. So in the B2 cell, you have to enter the formula =VLOOKUP($A2,Sheet2!$A$2:$D$18,2,0) and copy this to C2 and D2 cell. In this way, you will need to write the formula only once. But keep in mind before copying down the formula in C2 and D2 change the third parameter to respective column numbers 3 and 4 and then copy down. This will save a huge amount of time if you want to write more lookups to fetch data from different columns. By using this technique, you can avoid writing so many Vlookup and avoid unnecessary typing mistakes.
05 Vlookup clubbed with function column

In the above example, we have written the Vlookup only once and then copied across the columns. But still, we have to edit the third parameter to get the details. We can avoid that also if the data you want to get is continuous. In the above example the data is continuous, means the second column has the Products, third has the Quantity and the fourth column has Rate/Unit. Now in the above example change the third parameter to COLUMN() like this =VLOOKUP($A2,Sheet2!$A$2:$D$18,COLUMN(),0) and then copy it across till D column. Column function will fetch the column number of the current cell where the formula is typed in sheet1. This column number is utilized as the third parameter. Keep in mind this will only work if the data you want is in sequential columns in sheet2. Suppose if you want the data in between skipping some columns then it will not give correct results as Column function will give the column number in a continuous series. Also, you can change the Column function a little bit if the data you want begins from the third column. Then you can edit the Vlookup formula to =VLOOKUP($A2,Sheet2!$A$2:$D$18,COLUMN()+1,0). Here I have used COLUMN()+1 which will return 2 +1, means you will get number three and in turn will fetch the third column data. Also, you can change the column function to make it more useful. Instead of writing the Column function without the cell reference you can write with a cell reference. If we rewrite the above formula it will be like this =VLOOKUP($A2,Sheet2!$A$2:$D$18,COLUMN(C3),0). Column function will return 3 because C3 is the third column. Using column function like this is more useful and you can easily see which column it is referring to by just looking at the formula.
05 Vlookup to sum multiple columns

Example: Open the file Chapter 1.4 - Vlookupsum.xlsx Here we have students score on various subjects. Now by making use of array formula, you can easily find out the total of each student with a single formula. [example] An array formula can perform multiple calculations on one or more of the items in an array. Enter this formula =SUM(VLOOKUP(D9,$A$2:$F$7,{2,3,4,5,6},0)) in cell D10. To get the sum of all subjects for the student you have to specify the columns you want to sum in curly brackets and also put a sum function before Vlookup. Once you have typed the formula instead of hitting Enter key, you have to use Ctrl + Shift + Enter to convert to an array formula. If correctly done you can see two curly brackets at the beginning and end; you don't have to manually put the curly brackets. By specifying the third parameter in the curly brackets all the data from column B:F is included and the sum function will perform the sum. We have used a small example, but in the actual world, you will be comparing two excel sheets with big data fields. At that time you can easily find out the sum using this array function.
05 Vlookup with fourth parameter true for approximate match

Now we will look into the Vlookup formula with the fourth parameter as true. Again l'am writing the syntax for you to understand. Syntax: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) When you use the fourth parameter as true to fetch the details, data should be sorted in ascending order. Otherwise the whole lookup will give wrong results. But most of the time you cannot sort the data you receive because of other calculations or the data cannot be sorted for others to use. In this case, we can use use the fourth parameter as false and get the exact match without sorting. Now we will go through another use for lookup with an approximate match. Example: Open the file Chapter 1.5 - Vlookup True.xlsx Here we have students name in first column followed by their marks in B and C column. We have to find out their grades based on the grade criteria given on the right side from column E to G. [example] For example, if the marks scored is between 51 and 60 it is an E grade, if it is between 61 and 70 it is D grade, 71 to 80 is C grade, 81 to 90 is B grade and 91 to 100 is A grade. Now enter the Vlookup formula in C2 cell =VLOOKUP(B2,$E$2:$G$6,3, TRUE). Keep in mind that you have to type True or 1 as the fourth parameter. The main thing you have to remember here is the grade criteria in E column should be sorted because this is the second parameters first column. For approximate match lookup, second parameter data should be sorted in ascending order. Otherwise, the whole data you retrieve will go wrong. Here we have sorted out in ascending order from 51 to 91. Now against that, we have put the grades in G column. F column is shown for you to understand the grade range. We are not using that column for calculation. We will start with the first mark 73. When 73 is compared to E column, you can see there is no exact match for 73 and 73 comes between 71 and 81. So Vlookup will try to match the value and finds the highest value is 81. Since it is not exactly matching it will fall below to the lower value 71 and fetch the grade against that. Now for each grade, it will look for the exact match, if it is not there, then it will fall to the lowest value in the E column in between and fetch the grade.
05 Vlookup errors

#N/A errorIf you are using the fourth parameter as TRUE or omitted, your Vlookup formula may return the

#N/A error in two cases:
Lookup value is smaller than the smallest value in the lookup array
In the above example if the student's mark is less than fifty then the formula will return an error. Because the smallest grade value is set to fifty and if it goes below then there is no value specified below to get a match and we will get an error. Just change the student's mark for yourself to a value below fifty and see for yourself.

#N/A error in exact match Vlookup
If there is a typing error of reference cell or the lookup value, then this error will happen. Also if there is not an exact match, then it will return N/A error.

#REF error
Ref errors occur when you are referencing the column number which is outside the one you specified in the table array (the second parameter). For example, you have specified A2:B4 as second parameter and in the third parameter, you have specified 4 you will get an error as the second parameter has only two columns. You can specify either one or two if you have selected two columns in the table array.

#VALUE error Generally you will get #VALUE! error for any of these reasons. If the lookup value exceeds 255 characters. Or full path to the lookup workbook is not supplied. Or the col_index_num argument is less than 1. #NAME error NAME error appears if you accidentally misspelled the function’s name or if you don’t type the equal sign in the formula. So you have to check the spelling. Lookup column is not sorted in ascending order If the second parameter first column is not sorted, then it may throw up N/A error.

× DATE: Thu, Feb 06, 2020
× TIME: 9:38:44 pm
× BEHAVIOR:
on Twitter or sign-up for my monthly newsletter: