Compare data in Excel in two columns to find duplicates

Compare data in Excel in  two columns to find duplicates

To use a formula to compare the data in two columns:

1. In a new worksheet, enter the following data (leave column B empty):

   X1: 1   Y1:     Z1: 3
   X2: 2   Y2:     Z2: 5
   X3: 3   Y3:     Z3: 8
   X4: 4   Y4:     Z4: 2
   X5: 5   Y5:     Z5: 0
        

2. Type the following formula in cell B1:
   =IF(ISERROR(MATCH(X1,$Z$1:$Z$5,0)),"",X1)
3. Select cells Y1:Y5.
4. The duplicate numbers are displayed in column B, as in the following example:

   X1: 1   Y1:      Z1: 3
   X2: 2   Y2:2     Z2: 5
   X3: 3   Y3:3     Z3: 8
   X4: 4   Y4:      Z4: 2
   X5: 5   Y5:5     Z5: 0