How to match two columns in Excel

Question

i am trying to Match two columns, say column A (2, 3, 4, 5, 5, 6, 7) and column B (2, 3, 4, 5, 6, 7) using IF(ISERROR(MATCH(A1,$B$1:$B$8,0)),"",A1).

The formula matches all values correctly but for the fives. It does match both 5's in column A with the single 5 in column B. whereas I expected it to pick one 5 in column A and matching with that in column B leaving the other 5 pending. Anyone have an alternative formula to the one above? Thanks

Answer 1

Assuming you are copying the formula down in another column, you could use COUNTIF to see if the number has already occurred in that column:-

=IF(OR(ISERROR(MATCH(A2,$B$2:$B$7,0)),COUNTIF(C$1:C1,A2)),"",A2)

(I am adding headers and starting in row 2).