How to find duplicates in excel between two sheets

Answer

Finding duplicates between two Excel sheets requires lookup functions or conditional formatting rules that compare values across separate datasets. Excel provides VLOOKUP, XLOOKUP, and Conditional Formatting as primary methods for identifying matching entries between sheets within the same workbook.

How does VLOOKUP find duplicates between two sheets?

VLOOKUP searches for a value from one sheet in a specified range on another sheet and flags matches as duplicates. The function requires an IF and ISERROR wrapper to return clear labels instead of error values when no match exists.

The standard formula structure is:

=IF(ISERROR(VLOOKUP(A2,Sheet2!$A$2:$A$100,1,FALSE)),"Unique","Duplicate")

This formula checks cell A2 from Sheet1 against column A in Sheet2. The function returns "Duplicate" when a match exists and "Unique" when no match is found.

Steps to use VLOOKUP for duplicate detection

1. Open both sheets in the same workbook and name them clearly (e.g., Sheet1 and Sheet2).
2. Ensure the comparison data occupies the same column format on both sheets, such as column A starting from row 2 with headers in row 1.
3. Insert a helper column in Sheet1, such as column B, with the header "Duplicate Check".
4. Select cell B2 in Sheet1 and enter the formula: =IF(ISERROR(VLOOKUP(A2,Sheet2!$A$2:$A$100,1,FALSE)),"Unique","Duplicate").
5. Adjust the range Sheet2!A$2: A$100 to match your actual data range on Sheet2.
6. Use absolute references ($ signs) to lock the lookup range during copying.
7. Press Enter to execute the formula.
8. Copy the formula down to all rows by dragging the fill handle from the bottom-right corner of B2 or double-clicking it.
9. Verify results by checking known duplicate values.

Filtering duplicate results

Filter or sort column B to group all duplicate entries for review after applying the formula. Delete the helper column after completing the analysis when the column is no longer needed.

How does XLOOKUP find duplicates between two sheets?

XLOOKUP identifies duplicates by looking up values from one sheet in another with built-in error handling capabilities. This function offers more flexibility than VLOOKUP because it searches in any direction and handles #N/A errors natively through optional parameters.

The standard formula structure is:

=IF(ISNA(XLOOKUP(A2,Sheet2!$A$2:$A$100,Sheet2!$A$2:$A$100)),"Unique","Duplicate")

This formula checks cell A2 from Sheet1 against the range in Sheet2. The function returns "Duplicate" for matches and "Unique" for non-matches.

Excel version requirements for XLOOKUP

XLOOKUP requires Excel 365 (subscription version), Excel 2021 perpetual license, Excel 2021 for Mac, or Excel for the web. Users with Excel 2019 or earlier versions must use VLOOKUP instead because XLOOKUP is unavailable in those releases.

Steps to use XLOOKUP for duplicate detection

1. Open both sheets in the same workbook (e.g., Sheet1 and Sheet2) with data in matching column formats.
2. Add a helper column in Sheet1, such as column B labeled "Duplicate Check".
3. Select cell B2 in Sheet1 and enter: =IF(ISNA(XLOOKUP(A2,Sheet2!$A$2:$A$100,Sheet2!$A$2:$A$100)),"Unique","Duplicate").
4. Use absolute references ($ signs) for the Sheet2 range to maintain fixed references when copying.
5. Press Enter to test the formula.
6. Drag the fill handle from B2 down to cover all data rows or double-click for auto-fill.
7. Verify accuracy by checking known duplicate values.

How to use conditional formatting to highlight duplicates between two sheets

Conditional Formatting applies visual highlighting to duplicate values between sheets without requiring helper columns. This method uses a formula-based rule that evaluates each cell against a range on another sheet in real-time.

Steps to apply conditional formatting for cross-sheet duplicates

1. Select the range in Sheet1 to highlight, such as A2:A100 (excluding headers).
2. Navigate to Home > Conditional Formatting > New Rule.
3. Select "Use a formula to determine which cells to format".
4. Enter =COUNTIF(Sheet2!$A$2:$A$100,$A2)>0 in the formula box.
5. Adjust the Sheet2 range to match your actual data range.
6. Use $A2 format (absolute column, relative row) to ensure proper evaluation across all selected cells.
7. Click Format and choose a fill color (e.g., yellow) for highlighting.
8. Click OK to close the Format Cells dialog.
9. Click OK to apply the rule.

All cells in the selected range that match values in Sheet2 now display the chosen highlight color. The formatting updates dynamically when data changes in either sheet.

Managing conditional formatting rules

Access Home > Conditional Formatting > Manage Rules to edit existing rules, create additional rules for different match counts (e.g., highlighting cells with more than one match using >1), or reorder rule priority. Test the configuration by adding known duplicate values to confirm the highlighting activates correctly.

Which method to choose for finding duplicates between sheets

VLOOKUP works reliably across all Excel versions and provides clear text labels for duplicate identification through helper columns. XLOOKUP offers cleaner syntax with native error handling but requires Excel 365 or Excel 2021. Conditional Formatting provides immediate visual identification without helper columns and updates dynamically with data changes, making it suitable for ongoing monitoring of large datasets.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report