Excel VLOOKUP Tutorial for Beginners: Learn with Examples

[soybean]

VLOOKUP uses four arguments or pieces of data:

Step 3) First Argument: the first argument would be the cell reference (as the placeholder) for the value that needs to be searched or the lookup value. Lookup value refers to the data which is already available or data which you know. (In this case, Employee Code is considered as the lookup value so that the first argument will be H2, i.e., the value which needs to be looked up or searched, will be present on the cell reference ‘H2’).

 

[soybean]

Step 5) Third Argument: It refers to the column reference. In other words, it notifies VLOOKUP where you expect to find the data, you want to view. (Column reference is the column index in the lookup table of the column where the corresponding value ought to be found.) In this case, the column reference would be 4 as the Employee’s Salary column has an index of 4 as per the lookup table.

 

[soybean]

Step 6) Fourth Argument: The last argument is range lookup. It tells the VLOOKUP function whether we want the approximate match or the exact match to the lookup value. In this case, we want the exact match (‘FALSE’ keyword).

1. FALSE: Refers to the Exact Match.
2. TRUE: Refers for Approximate Match.
   

 

[soybean]

Step 7) Press ‘Enter’ to notify the cell that we have completed the function. However, you get an error message as below because no value has been entered in the cell H2i.e. No employee code has been entered in Employee Code which will allow the value for lookup.

 

[soybean]

However, as you enter any Employee Code in H2, it will return the corresponding value i.e. Employee’s Salary.

 

[soybean]

So in a brief what happened is I told the cell through the VLOOKUP formula is that the values which we know are present in the left-hand column of the data,i.e., depicting the column for Employee’s Code. Now you have to look through my lookup table or my range of cells and in the fourth column to the right of the table find the value on the same row,i.e., the corresponding value (Employee’s Salary) in the same row of the corresponding Employee’s Code.

The above instance explained about the Exact Matches in VLOOKUP,i.e., FALSE Keyword as the last parameter.

 

[soybean]

VLOOKUP for Approximate Matches (TRUE Keyword as the last parameter)​

Consider a scenario where a table calculates discounts for the customers who do not want to buy exactly tens or hundreds of items.

As shown below, certain Company has imposed discounts on the quantity of items ranging from 1 to 10,000:

Download the above Excel File

 

[soybean]

Now it is uncertain that the customer buys exactly hundreds or thousands of items. In this case, Discount will be applied as per the VLOOKUP’s Approximate Matches. In other words, we do not want to limit them for finding matches to just the values present in the column that are 1, 10, 100, 1000, 10000. Here are the steps:

Step 1) Click on the cell where the VLOOKUP function needs to be applied i.e. Cell reference ‘I2’.

 

[soybean]

Step 2) Enter ‘=VLOOKUP()’ in the cell. In the parenthesis enter the set of Arguments for the above instance.

 

[soybean]

Step 3) Enter the Arguments:

Argument 1: Enter the Cell reference of the cell at which the value present will be searched for the corresponding value in the lookup table.