Find duplicate values in two columns

This Excel tutorial shows how to find duplicate values in two columns with a formula that uses both the AND and COUNTIF functions. Once provided with the range of the two columns, the result will be returned as TRUE or FALSE.

Norder to find duplicate values in two columns, the conditional formatting is used with a formula which consist of the COUNTIF and AND function.

Formula or Syntax

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

Parameters to find duplicate values in two columns

These are the parameters required to find duplicate values in two columns:

  • range1 – It is the range of the cells in the first column.
  • range2 – It is the range of the cells in the second column.
  • criteria – It is the first cell name in the first column. It is constant between the two columns.

How to find duplicate values in two columns?

Let’s consider the example for better understanding on how to find duplicate values in two columns.

  • The sales made in a store are recorded with the names of the individuals that purchased from them for two days (two columns).

Figure 1. Sales record with duplicate values in two columns.

  • Let’s try and apply the formula to find the customers that appear in the two columns. The AND and COUNTIF functions are applied to check if Mary is a duplicate value that is, appears in the two columns.

Figure 2. The AND and COUNTIF functions to find a duplicate value in the two columns.

  • The formula returns TRUE which means that Mary appears in the two columns.

Figure 3. The result TRUE shows the value appears in the two columns.

  • Inputting the formula for each customer shows that Mary and Elizabeth are duplicate values in the two columns.

Figure 4. The results showing the duplicate values in the two columns.

To find duplicate values in two columns, the COUNTIF function returns the count of each value in the given range or column. The AND function ensures that the results of the two COUNTIF functions are the same, thereby returning TRUE. Otherwise, the AND function will return FALSE.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar