Buzz
In this context: Sheet1!B5 represents cell B5 in Sheet1 where data matching is to be checked.
Sheet2!$B$4:$G$33 is the range of data to search for matching data in Sheet 2.
1 indicates the column from which you want the Vlookup function to return a value within the range of data being checked.
0 denotes the exact match type for the Vlookup function.
For those unfamiliar with the Vlookup function, check out this article on how to use Vlookup effectively.
So, if the data matches, the function returns the matching data; if not, it returns the #N/A error.
How to write the Vlookup function
Since Vlookup operates on data from two sheets, each value should be preceded by the sheet name. You can enter the function as usual, or for more precision, follow this syntax:
1. Click on the mouse in cell C5 of Sheet 1 (the first cell to check for duplicate data), then select the Formulas -> Insert Function tab.
2. The Insert Function dialog box appears. In the Or select a category section (1), choose Lookup & Reference because the Vlookup function belongs to this group. By doing so, the Select a function section will narrow down the search to the Vlookup function. Next, select Vlookup in the Select a function (2) section and click OK.
3. The Function Arguments dialog box appears, where you will see the parameters in the Vlookup function. Here, select data for each parameter as follows:
- In the Lookup_value section, place the cursor here, then select cell B5 in Sheet 1. Now, Sheet1!B5 will appear in the Lookup_value cell.
- Next, in the Table_array section, place the cursor here and select sheet 2. Choose the data range to check for duplicate data. This will result in Sheet2!B4:G33 appearing in Table_array, with B4:G33 being the selected range.
- If you want to check multiple data, you need to fix this selection area by placing the cursor between or at the end of B4 and pressing the F4 key. Next, place the cursor at the end of G33 and press F4 to fix the position. Now, Table_array will be Sheet2!$B$4:$G$33 as shown below.
- Next, in the Col_index_num section, enter the column position you want to return data if two data are duplicate. For example, if you want to return the duplicate data itself, enter 1 to return the corresponding data in the first column. In the Range_lookup section, enter 0 for exact match. Then press OK to add the function to the selected cell.
So, you will get the result as below:
To verify the data below, simply copy the formula down as fixed ranges are set, ensuring the function remains unaffected by copying. You will receive duplicate data as a result, and non-duplicate data will return the error #N/A.
The ISNA function helps check for #N/A data. If the data is #N/A, ISNA returns TRUE; otherwise, it returns FALSE.
The IF function checks if a condition is true, returning a specified value if true and another specified value if false.
So in the example above, instead of just using the VLOOKUP function, you can combine the IF and ISNA functions to check the data. Enter the function formula as follows:
=IF(ISNA(VLOOKUP(Sheet1!B5;Sheet2!$B$4:$G$33;1;0));'Not';'Duplicate data')
The ISNA function checks the return value of the VLOOKUP function. If VLOOKUP returns #N/A, ISNA returns TRUE. VLOOKUP returns #N/A if the data is not duplicated. So, IF returns 'Not'.
If VLOOKUP returns a specific value, ISNA returns FALSE, and IF returns the false condition as 'Duplicate data'.
Similarly, copy the formula down the subsequent rows to achieve the following result:
Above, this article has shared with you how to filter duplicate data between 2 Sheets in Excel. So, you can quickly filter duplicate data between different sheets. Hope this article will be helpful to you. Wish you success!
