How do I find matching values in two columns in Excel?

Use =COUNTIF($B:$B,A2)>0 next to column A. TRUE means the value in A2 exists somewhere in column B. For case-sensitive matching, use =SUMPRODUCT(--EXACT($B$2:$B$100,A2))>0 instead.

How do I highlight values that appear in both columns in Excel?

Select column A, go to Home > Conditional Formatting > New Rule, choose Use a formula, and enter =COUNTIF($B:$B,A1)>0. Apply a fill color. This highlights every value in column A that also appears in column B.

What is the difference between finding duplicates within one column vs across two columns?

Within one column, a duplicate means the same value appears more than once in that single list. Across two columns, a duplicate means a value from column A also exists in column B — even if it only appears once in each. These are different problems and use different formulas.

How to Find Duplicate Values in Two Columns in Excel

Understanding the Problem: Within a Column vs Across Two Columns

There are two different problems often described as "finding duplicates in two columns," and they require different approaches:

Finding values that appear more than once within a single column — for example, an email address that appears twice in column A.
Finding values from one column that also exist in a second column — for example, which customer IDs in list A also appear in list B.

This guide focuses on the second scenario: comparing two lists to find where they overlap. If you need to find duplicates within a single column, see How to Remove Duplicates in Excel.

Method 1: COUNTIF Formula

The simplest approach is a COUNTIF formula in a helper column next to list A. With column A containing the first list and column B containing the second list, add this formula in column C starting at C2:

=COUNTIF($B:$B,A2)>0

This returns TRUE when the value from A2 exists anywhere in column B, and FALSE when it does not. Drag the formula down for all rows in column A.

To get a count of how many times the value appears in column B instead of a simple yes/no:

=COUNTIF($B:$B,A2)

A result of 0 means the value is unique to column A. A result of 1 or more means it also appears in column B.

Method 2: Conditional Formatting to Highlight Matches

Conditional formatting is the fastest visual approach when you want to see at a glance which values overlap:

Select the cells in column A that you want to check (for example, A2:A500).
Go to Home > Conditional Formatting > New Rule.
Choose Use a formula to determine which cells to format.
Enter: =COUNTIF($B:$B,A2)>0
Choose a fill color and click OK.

Every value in column A that also appears in column B will be highlighted. Values unique to column A remain unhighlighted.

To also highlight from the column B perspective (which B values exist in A), repeat the steps for column B with the formula =COUNTIF($A:$A,B2)>0.

Method 3: IF Formula for a Labeled Result

If you prefer a readable label instead of TRUE/FALSE, wrap the COUNTIF in an IF:

=IF(COUNTIF($B:$B,A2)>0,"In both lists","Unique to A")

This makes the output easier to read in reports, when sharing the file with others, or when filtering the helper column to see only matched or unmatched rows.

Method 4: MATCH Formula (Returns Position)

MATCH is useful when you need to know not just whether a value exists in column B, but which row it appears in:

=MATCH(A2,$B:$B,0)

This returns the row number within column B where the value was found, or an #N/A error if it was not found. Wrap in IFERROR for a cleaner result:

=IFERROR(MATCH(A2,$B:$B,0),"Not in B")

The row number is useful when you want to pull additional data from that row using INDEX, or when you need to verify that the match is in the correct position.

Method 5: VLOOKUP to Pull Data From the Matching Row

When you find a match, you often want to pull additional information from the matched row in column B. VLOOKUP can do this in one step:

=IFERROR(VLOOKUP(A2,$B:$D,2,0),"No match")

This looks up the value in A2 within column B, and if found, returns the value from the second column of the range B:D (which is column C). Change the column index to return other fields. For example, 3 would return column D.

Note: VLOOKUP returns only the first match. If a value in column A matches multiple rows in column B, only the first match is returned. Use COUNTIF first to verify that only one match exists before relying on VLOOKUP to pull data.

Method 6: Normalize Values Before Comparing

Two values that look the same may not match because of differences in spacing, capitalization, or number formatting. Before comparing two columns, normalize both:

=COUNTIF($B:$B,TRIM(LOWER(A2)))>0

This trims spaces and converts to lowercase before comparing. For this to work, the values in column B should also be normalized. A helper column with =TRIM(LOWER(B2)) applied to column B makes the comparison reliable for text data like names, email addresses, and category labels.

For numeric IDs that might be stored as text in one column and as numbers in another, use:

=COUNTIF($B:$B,TEXT(A2,"0"))>0

Method 7: Find Values in A That Are NOT in B

The inverse question — which values in column A do not appear in column B — is equally common. Use:

=COUNTIF($B:$B,A2)=0

TRUE means the value from column A is missing from column B. This is useful for identifying:

Customers who placed orders in January but not February.
Products in the catalog that are not in the inventory export.
Employees in one HR system that do not appear in the payroll system.

Filter the helper column for TRUE to see all the values unique to column A.

Method 8: Use a Duplicate Finder for Multi-Column Matching

The COUNTIF approach works well for single-column comparisons. But when a duplicate is defined by a combination of columns — for example, email address plus order date, or customer ID plus product code — a formula approach becomes complex. The Duplicate Row Finder lets you select multiple columns that define a match and previews the duplicate groups before you decide what to do with them.

Before You Delete Matching Rows

Finding that a value appears in both lists does not automatically mean one copy should be deleted. Consider these common scenarios where both copies are valid:

A customer placed multiple orders — the same customer ID appears in both the January and February export.
A product appears in multiple regional catalogs — the same SKU appears in both the EU and US product list.
An email is used for two different account types — both are legitimate and should both be kept.

Before deleting, filter the matching rows and review them to confirm which instances represent actual errors versus valid repeated entries.

Frequently Asked Questions

Why does COUNTIF return wrong results when comparing columns?

Common reasons: values in one column have extra spaces (use TRIM), values are stored as text in one column and as numbers in the other (use VALUE or TEXT to normalize), or the comparison is case-sensitive when you expected case-insensitive (COUNTIF is already case-insensitive, so if mismatches persist, the difference is in spacing or formatting, not case).

How do I find duplicates across two separate workbooks?

Copy one list into a new sheet in the same workbook first, then use COUNTIF referencing that sheet. COUNTIF can reference ranges on other sheets within the same workbook using the format =COUNTIF(Sheet2!$A:$A,A2). Referencing a closed external workbook directly with COUNTIF is not supported.

Can I compare more than two columns at once?

Yes. Create a helper column that concatenates the key fields with a separator: =A2&"|"&B2. Do the same in the second dataset. Then use COUNTIF to compare the concatenated helper columns. The separator prevents false matches where fields like "AB" + "C" would otherwise collide with "A" + "BC".