Home > QA > VLOOKUP in microsoft excel

VLOOKUP in microsoft excel

What is VLOOKUP?

VLOOKUP stands for Vertical lookup.Vlookup is a function available in the excel – which utilized to compare two columns in the same excel sheet or between two different excel sheets. The user can compare the data when it is arranged vertically.

How to use Vlookup?
a. Vlookup within the same excel
For example – We can find the value of Employee EMI against the employee name from the given Employee loan detail sheet.
The below mentioned excel is specifying the details for the employee details and employee loan details.
1. Select the cell from where the EMI of the employee reflects here the cell = D13
2. Select function = VLOOKUP from excel functions or enter =vlookup in the cell
3. Now we need to add arguments in the vlookup brackets as mentioned below
i. First argument = Reference cell against which the value to be searched here that will be “Employee Name” and cell refernce = D12
ii. Second argument = Range of the table from which the data need to be searched here we have considered entire table for the same range = A2:G8
iii. Third argument = The reference of the column number from where the corresponding data needs to be fetched; here the column number = 6
iv. Forth argument = To specify the match needs to exact or approximate. For exact match keyword “False” should be used and for approximate match the keyword will be “True”. Here we need to find an exact match hence the value = FALSE

So the vlookup will be =VLOOKUP(D13D12,A2:G8,6,0)

 

b. Vlookup between two different excel sheets
For example, if we have two different excel sheets, one with employee personal details while second excel sheet will have employee loan details and we need to find the Employee EMI from the one excel against the Employee name
1. Select the cell in which the vlookup function needs to apply
2. Select function = VLOOKUP from excel functions or enter =vlookup in the cell in the excel in which you need data to fetch
3. Now we need to add arguments in the vlookup brackets as mentioned below
i. First argument = Select the common reference cell from both of the excel – here we have employee name is a common column in both of the excels hence select the first cell of the column = A2
ii. Second argument = Range – The range in the case of two different excels will be all the columns in the excel from where the data is needed to fetch i= [Employeeloandetails.xlsx]FALSE!$A:$H
iii. Third argument = The reference of the column number from where the corresponding data needs to be fetched; here the column number = 7
iv. Forth argument = To specify the match needs to exact or approximate. For exact match keyword “False” should be used and for approximate match the keyword will be “True”. Here we need to find an exact match hence the value = FALSE/0

Here the VLOOKUP string will be =VLOOKUP(A2,[Employeeloandetails.xlsx]FALSE!$A:$H,7,0)


Advantage of vlookup-
1. User able to compare the data easily within or different excel sheets
2. Can find exact as well as approximate match
3. Vlookup is a less time consuming process to compare huge data
4. Vlookup can be used inside other functions too

This Article is TAGGED in , . BOOKMARK THE permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">