Articles → EXCEL → Vlookup In Excel

Vlookup In Excel

In this tutorial I will explain about Vlookup function in excel.

Software Requirement

Before you start reading this tutorial you should make sure that excel 2007 (or above) is installed on your machine.

Technical Knowledge

In order to understand this tutorial you should know basics about excel.

What Is A Table Array?

Table array means range of cells in excel which represents data in tabular form. See the screen shot below

Picture showing the sample data in the excel file

Click to Enlarge

Here we have a range of cells from A1 to B7.

What Is Vlookup Function?

Vlookup function means searching the value in left most column of table array. Once the value matches, Vlookup function returns the value of specified column number (mentioned as index number in Vlookup function) of the same row.

Syntax

VLOOKUP( value, table_array, index_number, [not_exact_match] )

Value - value to search in the first column of the table array.
Table array - range of cells in excel which represents data in tabular form (as mentioned in the section above)
Index number – Column number in table array from which matching value must be returned.
Range look up - A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match.

Example 1

In the first example we have a table array which contains list of stationary and their respective prices as shown in figure below

Click to Enlarge

Now our requirement is to get the price of the product based on the product name. Let us see how we can get this using Vlookup function.

First of all select any 2 cells in the excel file. For this example I am using A11 and B11.
Go to B11 and write the following formula

=VLOOKUP(A11,A3:B7,2,FALSE)

            - Here A11 is the value which we will search in the table array. This value will be entered by the user.

            - A3:B7 is the table array. It is important to note we have not included headers in the range.

            - 2 is the column number of table array. It means that once the look up value matches any row in the left most column of table array then return the value of column number 2 of the same row.

            - Fourth parameter is false which means we need an exact match.

Once you write the formula you will get the following output.

Picture showing the N/A value when vlookup function is applied

Click to Enlarge

The reason for this output is that we have not entered any product name in A11. Once we enter the product name in A11, we could see its price in B11.

Picture showing the output of vlookup in excel

Click to Enlarge

Example 2

In the second example we will add duplicate values in product name and check the output. Consider the sample table array below

Click to Enlarge

Here we have added ‘Pen’ twice. So the same expression will return the first matched value.

Click to Enlarge

Example 3

In the third example we will change the value of last parameter in Vlookup function i.e. range look up. In the first 2 examples we have set the value of range look up as FALSE - Exact Match. Let’s change the value to TRUE - Approximate match. Consider the sample table array below

Click to Enlarge

Now create some data in excel

Click to Enlarge

Here we have a list of students along with their marks. Now based on the marks we want to give them the grades.

No add one more column ‘Grade’ and write following formula in O3 (row where student name is Sumit). And then drag it to all the rows

=VLOOKUP(N3,Grades,2,TRUE)

You will get following output

Click to Enlarge

This is because of range look up value. If the value is set to TRUE then, Vlookup function matches the next largest value less than look up value. Take the first look up value i.e. 34 in the figure above. In table array 33 is largest value lesser than look up value (i.e. 34).

Posted By -	Karan Gupta

Posted On -	Thursday, July 17, 2014

Query/Feedback

Your Email Id		**

Subject		*

Query/Feedback	Characters remaining 250	**