There are times when we might have to compare more than one column to find if there are any repeated data. For example , I have the list of actors in the column A and column B and I want to list out all the actors names that are found in both the columns.

How to find duplicate values in multiple columns in Excel 2016?

Assuming that the column A and B contains the list of actors and you need to display the duplicate entries or the names of the actors appearing in both the columns in the Column C.

In the cell C1 , enter the following formula

=IF(ISERROR(MATCH(A1,$B$1:$B$13,0)),””,A1)

The formula simply matches the value from cell A1 with the range of cells from B1 to B13 and displays the empty text if it does not exist in both columns , else displays the cell value.