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):

?

1 2 3 4 5

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:

?

1	=IF(ISERROR(MATCH(X1,$Z$1:$Z$5,0)),"",X1)

3)Select cells

?

1

Y1:Y5.

4)The duplicate numbers are displayed in column B, as in the following example:

?

1 2 3 4 5

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