Excel: Find Duplicate Values with VLookup in different sheet

Functions plays an important role during doing complex calculations. VLookup is an important lookup function at this time. Today we discuss another important topic of excel find duplicate Values with VLookup in different sheet.

All we know importance of MS Excel. MS Excel is an electronic spreadsheet application software which is used for data analysis, modelling, representing numerical data in term of chart, provide quick results of complex calculations using different commands and functions.

We have to face problem to search lot of duplicate records in any worksheet. If you try to find them one by one manually then it will take lot of time. Excel find duplicate Values with VLookup in different sheet guide solve your lot of problems.

This question is faced by lot of persons when they go for interview for the job in any company or facing the questions about the which is related to “VLookup” function during the interview session. You know very well “VLookup” is one of the most important functions which are found in “Lookup and Reference” category of Microsoft Excel. There are lots of different examples of VLookup function in Excel, which you can use in different situation.

How to find duplicate Values with VLookup in different sheet in MS Excel:

When we are using “VLookup” function, most of the cases we use “Exact Match” method in MS Excel. Today I try to tell you how to use VLookup” function for searching “Duplicate Values with VLookup in different sheets” in range lookup. We already discuss how to search duplicate values with VLookup function in same sheet in MS Excel.

Definition of VLookup function:

This function is found in the “Lookup and Reference” categories of Microsoft Excel So have a look and take few easily use appropriate match in range lookup. How could you find any duplicate values in a huge database with the help of VLookup function in MS Excel.

In that situation, if you don’t give proper answer then you will be rejected for the job during facing the interview session but if you have proper knowledge about the all-important commands and what is the use of VLookup function in MS Excel then you will be able to give exact answer of any query and you will be selected for the job. So, due to watching the importance of ” VLookup” function today, we have to discuss How to find duplicate values using VLookup function in MS Excel in this article.

Must Read: Hindi Typing- Steps for Learning Effortless Hindi typing

Syntax of VLookup function:

The Microsoft Excel VLookup function searches a value in the extreme left column of the table_array and provides the value in the same row which is based on the index number.

VLookup (lookup_value, table_array, col_index_num, [range])

Note: Before using the VLookup function, first you need to arrange first column of a table or array in the ascending order, otherwise VLookup function is not able to provide the accurate result.

Lookup_value: The value to be searched in the extreme first column of the table array.

Table_array: Two or more columns of data. Give cell reference of a range / range name.

Col_index_num: The column index_number in the table from which the searched value must be returned.

If col_index_num of returns 1 the value in the first column in table_array.

If col_index_num is: Less than 1, this function returns an error value the #VALUE!.

If it is a greater than the number of columns in table_array, this function returns error value #REF!.

Range_lookup: It is an options part of this function. There are two options available in this part.

TRUE: Appropriate Match: This option work properly when first column of your table will be sorted in ascending order only otherwise it displays wrong
result.

False: Exact Match: VLookup by default check only on exact match method.

For E.g: If you have a huge database or query or table and now you want to search duplicate values using VLookup function in different sheet in MS Excel then you have to take following few important steps which is given below:

Steps to find Duplicate Values with VLookup Function in Different Sheet in MS Excel:

Step 1: Create the following table on Sheet1 and Sheet2 For E.g.:
Sheet1, A1:A7={“Members List 1″,”Amit”,”Atul”,”Suresh”,”Sanjay”,”Anuj”,”Dinesh”}
Sheet 2, A1:A7={“Members List 2″,”Lucky”,”Tim”,”John”,”Randy”,”Pol”,”Amit”}

Note: After creating this table, If someone ask you find out the duplicate value in both range which is given on the different worksheet using VLookup function in MS Excel. Notice that both ranges have a common name “Amit” and now watch the process how to found this duplicate value using VLookup function in different worksheets in Microsoft Excel.

Step 2: Take the cursor on a particular location where you want to view the result after applying the VLookup function. For E.g. type Result in the cell C1 on Sheet1.

Step 3: Now, take cursor on cell C2 and type the following given formula to searching duplicate value using VLookup function on different worksheet in MS Excel:

=IF(ISERROR(VLOOKUP(A2,Sheet2!$A$1:$A$7,1,0)),”Unique”,”Duplicate”)

after typing the formula press enter or return key, MS Excel shows you result Duplicate on “Amit” using the VLookup function on the different worksheets, because both worksheets hold “Amit” name in different sheets .

Note: During typing the VLookup formula you have to change the relative reference to absolute reference by pressing F4 function key to change the cell reference and fix the range of Tally_array.

Step 4: Again select the cell C2 and drag the fill handle up to cell C7 and release the mouse pointer. Now, you will get rest of the cells shows “Unique” as a result because there is no another duplicate value in both range of cells in different worksheets, but if you make any kind of modification in the second range of A1:A7 your answer will be changed.

Must Read: Vlookup merge data from one sheet to another Excel worksheet

I hope you like this, How to find duplicate Values with VLookup in different sheet guide from which you will be able to easily find duplicate Values with VLookup in different sheet. Please give your important suggestion regarding this article in the comment box and click on the like or share button to help others who are facing problem for searching duplicate values using ” VLookup” function in MS Excel. Thanks to all.