ISError function is a built-in function in Excel. Method 3: Identify Duplicate Values using ISERROR and Match function Step 2: Now, Select the cell L2 and drag the VLookup function from L2:L11. Now press the enter key to get the result. Step 1: Type the following given countif function in cell L2 =IF(COUNTIF($A$2:$A$11,Sheet2!A2)=1,”Duplicate”,”Unique”). Range: It is the range of continuous cells which you want to evaluated for the specific condition.Ĭriteria: It is the condition in the form of any number, expression or text value that define which cells to be counted. Syntax of Countif function: COUNTIF (range, criteria) You can easily count all those cells that meet the criteria from given cells values in any worksheet. Method 2: Search Duplicate Values in Excel using Countif functionĬountif is an important function of Excel. Step 3: Drag the VLookup function from K2:K11 to get the result for rest of the cells. Step 2: Type the following VLookup function in cell K2 =IFERROR(VLOOKUP(Sheet2!A:A,A:A,TRUE,FALSE),”-“). Be ensure that both worksheets are exists within a same workbook. Step 1: Fill the given information in two worksheets. Prevent duplicate values with Data Validation in MS Excel.TRUE: Appropriate Match: This option work properly when first column of your table will be sorted in ascending order only otherwise it displays wrongįalse: Exact Match: VLookup by default check only on exact match method.
There are two options available in this part. Range_lookup: It is an options part of this function. Sometime it is a greater than the number of columns in table_array, this function returns error value #REF!. If col_index_num is: Less than 1, this function returns an error value the #VALUE!. If col_index_num of returns 1 the value in the first column in table_array. Give cell reference of a range / range name.Ĭol_index_num: The column index_number in the table from which the searched value must be returned. Table_array: Two or more columns of data. Lookup_value: The value to be searched in the extreme first column of the table array. 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. Syntax of VLookup function: VLookup (lookup_value, table_array, col_index_num, ) 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. Method 1: Find Duplicate Values in Excel using VLookup function Now, there is no need to worry after reading this guide you can easily search duplicate values in two excel worksheets. Most of the users who are working on the Excel sheet has to face problem during find duplicate values in Excel worksheets. How to Identifying Duplicate Values in two Excel worksheets