Excel formula: Find duplicate values in two columns

Generic formula

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

Explanation

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.

How this formula works

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.

Author

Dave Bruns

Related formulas

Highlight duplicate values

Note: Excel contains many built-in "presets" for highlighting values with conditional formatting, including a preset to highlight duplicate values. However, if you want more flexibility, you can highlight duplicates with your own formula, as...

Highlight duplicate rows

Excel contains a built-in preset for highlighting duplicate values with conditional formatting, but it only works at the cell level. If you want to highlight entire rows that are duplicates you'll need to use your own formula, as explained below. If...

List contains duplicates

Does a range contain duplicate values? If you want to test a range (or list) for duplicates, you can do so with a formula that uses COUNTIF together with SUMPRODUCT. In the example, there is a list of names in the range B3:B11. If you want to test...

Related functions

Excel COUNTIF Function

COUNTIF is a function to count cells that meet a single criteria. COUNTIF can be used to count cells with dates, numbers, and text that match specific criteria. The COUNTIF function supports logical operators (>,...

Excel AND Function

The 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 make more powerful conditional formatting rules...

How to make conditional formatting variable

In this video, we look at how to make conditional formatting variable, so that you can change how formatting is applied without editing a rule. This elegant technique helps you build killer spreadsheets that are fun to use. ...

How to highlight rows with conditional formatting

In this Exceljet tip, we show you how to highlight entire rows in a list using conditional formatting with a formula. This is a handy way to visually expose information you are interested in without having to apply a filter. We also show you how to...

How to build a search box to highlight data

In this video, we use conditional formatting to create an interactive search box to highlight data. This is a nice alternative to filtering, because the information you're looking is highlighted in context, and the search box checks multiple...

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.