What is the excel formula to find duplicates in two columns

Answer

What is the primary formula to find duplicates in two columns?

The primary Excel formula for finding duplicate values across two columns is MATCH combined with ISERROR or IF. This formula checks whether a value from one column exists in the other.

Recommended Formula

Use the following formula in column C next to data in column A, scanning column B for matches:

=IF(ISERROR(MATCH(A2,$B$2:$B$100,0)),"Unique","Duplicate")

This flags values in column A that appear anywhere in column B.

Basic Syntax

The core syntax is IF(ISERROR(MATCH(lookup_value, lookup_array, 0)), "Unique", "Duplicate"). Use absolute references (B$2: B$100) to lock the search range when dragging down. Swap columns A and B for bidirectional checks, or use COUNTIF(B:B,A2)>0 for count-based detection.

What are alternative formulas to find duplicates across two columns?

Yes, alternatives to MATCH/ISERROR exist for finding duplicates across two columns. These include COUNTIF, SUMPRODUCT, and dynamic array functions like FILTER or XLOOKUP.

COUNTIF Method

COUNTIF checks one column against the other:

=IF(COUNTIF($B$2:$B$100,A2)>0,"Duplicate","Unique")

SUMPRODUCT with EXACT (Case-Sensitive)

SUMPRODUCT with EXACT enables case-sensitive matching:

=IF(SUMPRODUCT(--(EXACT($B$2:$B$100,A2)))>0,"Duplicate","Unique")

FILTER Function (Excel 365)

FILTER extracts matches dynamically:

=FILTER(A2:A100,(COUNTIF(B2:B100,A2:A100)>0)*(A2:A100<>""))

XLOOKUP Method

XLOOKUP flags matches in Excel 365:

=IF(ISNA(XLOOKUP(A2,$B$2:$B$100,$B$2:$B$100)),"Unique","Duplicate")

What are non-formula methods to find duplicates in two columns?

Conditional Formatting

Conditional Formatting highlights duplicates visually. Select both columns, then navigate to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

VLOOKUP

VLOOKUP provides a legacy alternative for flagging matches between columns, though XLOOKUP offers improved functionality in modern Excel versions.

When should you use each duplicate detection method?

How do you find duplicate rows across two columns?

Users can identify duplicate rows across datasets by comparing entire rows (matching both Column A and Column B values simultaneously) using COUNTIFS for multi-criteria checks or helper columns with concatenation.

COUNTIFS Method

The primary approach uses COUNTIFS, assuming dataset 1 in columns A:B and dataset 2 in columns D:E:

=IF(COUNTIFS($D$2:$D$100,$A2,$E$2:$E$100,$B2)>1,"Duplicate row","Unique")

This flags rows where both columns match any prior row.

Helper Column with Concatenation

Create a helper column with concatenation in column C:

=A2&"|"&B2

Then apply the duplicate check:

=IF(COUNTIF($C$2:$C$100,C2)>1,"Duplicate","Unique")

The pipe character "|" serves as a delimiter to avoid false matches from numeric concatenation.

FILTER with COUNTIFS (Excel 365)

Dynamic arrays enhance multi-column matching:

=FILTER(A2:B100,(COUNTIFS($D$2:$D$100,A2:A100,$E$2:$E$100,B2:B100)>0)*(A2:A100<>""))

What are the considerations for finding duplicates in two columns?

Data Type Consistency

Ensure both columns contain the same data types. Numbers stored as text in one column will not match true numbers in another column. Convert data types using VALUE or Text to Columns before applying formulas.

Whitespace Issues

Leading or trailing spaces cause mismatches. Apply TRIM to clean data in both columns before comparison.

Case Sensitivity

Standard COUNTIF and MATCH functions are case-insensitive. Use SUMPRODUCT with EXACT for case-sensitive duplicate detection.

Performance with Large Datasets

COUNTIFS and MATCH perform efficiently in large datasets. Avoid volatile functions or entire column references (A:A) when working with datasets exceeding 100,000 rows. Use defined ranges instead.

Preserving Data for Audits

Use formula-based methods or Conditional Formatting when preserving all duplicate occurrences is required. Avoid Remove Duplicates for audit-sensitive datasets.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report