Excel formula: Find duplicate values in two columns

Generic formula

=AND(COUNTIF(range1,A1),COUNTIF(range2,A1))

Summary

To highlight duplicate values in two or more columns, you can use conditional formatting with on a formula based on the COUNTIF and AND functions.

In the example shown, the formula used to highlight duplicate values is:

=AND(COUNTIF(range1,B5),COUNTIF(range2,B5))

Both ranges were selected at the same when the rule was created.

Explanation

This formula uses two named ranges, "range1" (B5:B12) and "range2" (D5:D10).

The core of this formula is the COUNTIF function, which returns a count of each value in both range inside the AND function:

COUNTIF(range1,B5) // count in range1
COUNTIF(range2,B5) // count in range2

COUNTIF will either return zero (evaluated as FALSE) or a positive number (evaluated as TRUE) for each value in both ranges.

If both counts are positive (i.e. non-zero), the AND function will return TRUE and trigger the conditional format.

Quick Start | More Examples | Troubleshooting | Training

Author

Dave Bruns

Related formulas

Highlight duplicate values

COUNTIF simply counts the number of times each value appears in the range. When the count is more than 1, the formula returns TRUE and triggers the rule. When you use a formula to apply conditional formatting, the formula is evaluated relative to...

Highlight duplicate rows

In the formula, COUNTIFS counts the number of times each value in a cell appears in its "parent" column. By definition, each value must appear at least once, so when the count > 1, the value must be a duplicate. The references are carefully locked...

List contains duplicates

Working from the inside out, COUNTIF first gets a count of every value in B3:B11 in the range B3:B11. Because we supplying a range (array) of cells for the criteria, COUNTIF returns an array of counts as a result. In the example shown this array...

Related functions

Excel COUNTIF Function

COUNTIF is an Excel function to count cells in a range that meet a single condition. COUNTIF can be used to count cells that contain dates, numbers, and text. The criteria used in COUNTIF supports logical...

Excel AND Function

The Excel AND function is a logical function used to require more than one condition at the same time. AND returns either TRUE or FALSE. To test if a number in A1 is greater than zero and less than 10, use =AND(A1>0,A1...

Related videos

How to apply conditional formatting with a formula

In this video, we'll look at how to use a simple formula to apply conditional formatting. Formulas allow you to create more flexible and powerful conditional formats.

Conditional formatting based on a different cell

In this video, we'll look at how to apply conditional formatting to one cell based on values in another, using a formula.

How to highlight rows with conditional formatting

In this video, we'll show you how to use a formula with conditional formatting to highlight an entire row in a multi-column list.

How to build a search box with conditional formatting

In this video, we'll look at a way to create a search box that highlights rows in a table, by using conditional formatting, and a formula that checks several columns at once.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.