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.

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.

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.

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.

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.

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.**#N/A error**If 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.