Look at "VLOOKUP" in MS Excel..

Is life running with MS Excel on week days..?? Having a big mess to sync 2 excel workbooks.?? Looking for the best option to reduce your work & save your time.?? The perfect solution for this is "VLOOKUP".. VLOOKUP is something easy to understand & difficult to implement.. Here are the tips that can simplify your work with the magical function "VLOOKUP".. Let us get into more details..

VLOOKUP :

This simplified meaning of this function "VLOOKUP" is to match the data that exists between two Excel Workbooks.. In VLOOKUP function V stands for Vertical.. VLOOKUP is all about to retrieve the data that you are searching between 2 Excel Workbooks in a vertical manner (Column Wise).. Here the term EXCEL SHEET is different from EXCEL WORKBOOK.. A workbook consists of Excel sheets.. All sheets combine and form an EXCEL WORKBOOK..We have many types of VLOOKUP function in EXCEL like matching the values in the same Excel sheet, matching the values between two Excel sheets & matching the values between two Excel Workbooks..In this blog, I have given a detailed explanation of "Matching the values between two Excel Workbooks."

SYNTAX:

The syntax for this function is =VLOOKUP(Lookup value, Table array, Col index num, Range lookup)

Lookup Value: Enter the value that you are looking for a match in another workbook (or) select the values in a column that you need to match with the data present in another Excel Workbook.

Table Array: Select the data in the second Excel workbook with 2(or)more columns.

Col index num: Column index number is to represent the number of columns that you have selected in the second Excel Workbook..

Range Lookup: This term is to define the find out the EXACT MATCH (or) APPROPRIATE MATCH. If you represent Range Lookup = FALSE --- It shows the exact match of your search

                                                Range Lookup = TRUE --- It shows the appropriate match of your search.

Yes..The formula is very clear now & it was a bit confusing while we implement..Here are the tips to follow and to implement in a perfect manner with a pictorial representation..

I had data of 2 companies with 7 employees and now I wanted to check how many employees in Company A have the same employee codes & with their different/same qualifications as the employees in Company B with the help of VLOOKUP function.

•  Firstly, open the excel workbooks of Company A & Company B.

• Now, I wanted to match employee codes with their qualification. So, I started opening the Excel of Company A, selected the output column to be displayed & continued to write the function. Then I have selected the entire Employee code column and the value of the column automatically will be represented in the function after your selection.

• The second step is to select the file/workbook that you need to match these details..In this case, the other file/workbook is Company B & details to be matched are "The same employee codes are to be matched with their qualification." So that I have selected both the columns of employee code & qualification and the value of these columns is automatically shown in the formula bar.

 

• The third step is to enter the col index number, this indicates the number of columns that we have selected in the Company B file/workbook. As we have selected 2 columns the value will be "2". Remember that every value should be separated with a comma(,)

• Now, enter the fourth value..If you are looking for an Exact Match then type FALSE, else type TRUE for an appropriate match and press enter button.

• Now, we are ready with the formula, and let's see whether our required output i.e., Matched Values of Employees Codes with their Qualification between both the companies are to be displayed.

The above output says there are 4 employee codes in Company A that have matched with other Company B and it represented the qualification of those matched employee codes as its output. The unmatched outputs are left with #N/A which meant No Match Applicable.. Initially, the output will be displayed for the first one, then drag and stop where you need output. Then output for all the selected ones will appear.. If the formula is executed in a perfect way we get these outputs.. If there is any error in the Syntax, then it results in #Ref (or) #Value.. This might be difficult for 2 times, but this gives the best results if you practice well. Do practice this exercise & get better results for your work.. 

I have taken an example with less data to keep it in a crystal clear manner..We can execute this to huge data so that we can save our time..Also, comment about your experience when you have practiced this function in Excel in the blog below.. HAPPY LEARNING..!!!!!!

                                                                                              ------- By Mounika Perumalla