Comparing two columns in Excel with exclusion

Question

I want to compare values in two columns in Excel as depicted in the image below :-

Using the formula, I want to put the values in the "Values of A which don't exist in B" and "Values of B which don't exist in A". Any help is appreciated.

I have shared the same excel sheet here.

Solution

The following will work - for each, add the formula in row 2 and then drag down

Values of A that do not exist in B

=IF(ISERROR(MATCH($A$2:$A$20,$B$2:$B$17,0)),A2,"")

Result = x, y, z, i, j ,k, l, u

NB: Your example spreadsheet is incorrect as u is in Col A but not Col B but you do not list it in your result set in Col C

Values of B that do not exist in A

=IF(ISERROR(MATCH($B$2:$B$17,$A$2:$A$20,0)),B2,"")

Result = q, r, e, f, g

Comments

[Ashish Gupta]

What does $ signify? When you write the in the the first cell the $ does not get prefixed with it. I had to put that manually.

[Alex P]

The $ makes the ranges absolute (i.e. A2:A20 and B2:B17). What this means is that when you drag down the formula the references remain A2:A20 and B2:B17. If you didn't do this the ranges would increment i.e. A2:A20, A3:A21, A4:A22. Try the formula without the $ signs and you will see what I mean...

[rogerstone]

Can you explain why absolute range is required for the value parameter in the match function? This works for me:-=IF(ISERROR(MATCH(A2,$B$2:$B$17,0)),A2,"")

[iowatiger08]

I know this is bit dated but your function helped but with Excel 2016, I did the direct cell to column compare without the $ and dragged the formula down for the rest. Otherwise it gave me some false mismatches =IF(ISERROR(MATCH(A1,B:B,0)),A1,"")