Excel: Find values in one column that are not in another

A quick note on how to compare two columns for values that are not found in another. I have a column with old values, and now that I have a new list, I want a quick way to see what values are in the old column and which ones are new...

Consider the 3 following columns in an Excel spreadsheet:
Old       New       Exists in Old?
--------- --------- --------------
123456    234567
234567    345678
345678    456789
567890    597890
I want the third column to say whether this is new or not.

I found this in a StackExchange site:

Method #1
=MATCH(B2, $A$2:$A$100, 0)

-- Check whether value in B2 exists in range of A2:A100.
-- Returns the index of column A in which the B2 value was found.
-- Returns #N/A for a value which is NOT in column A.
Drag the formula in B2 down all the list as per the following screenshot. A value equal to "#N/A" means the value was NOT found in column A.

Values found in one column but not another

Method #2
Be careful with this one because if you copy the formula down, it may automatically modify the range. So the formula in the first row will be =COUNTIF(A2:A100, B2) but the formula in the second row will be =COUNTIF(A3:A101, B3):
=COUNTIF(A2:A100, B2)

-- Returns 1 if value in B2 was found in range A2:A100
-- Returns 0 if value in B2 was not found in range A.

Related Articles

Joes Revolver Map

Joes Word Cloud


Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Donate to Joel Lipman via PayPal

Donate to Joel Lipman with Bitcoin - Valid till 8 May 2022 3QnhmaBX7LQSRsC9hh6Je9rGQKEGNQNfPb
© 2021 Joel Lipman .com. All Rights Reserved.