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...
Why?
Consider the 3 following columns in an Excel spreadsheet:
copyraw
I want the third column to say whether this is new or not.Old New Exists in Old? --------- --------- -------------- 123456 234567 234567 345678 345678 456789 567890 597890
- Old New Exists in Old?
- --------- --------- --------------
- 123456 234567
- 234567 345678
- 345678 456789
- 567890 597890
How?
I found this in a StackExchange site:
Method #1
copyraw
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.=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.
- =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.
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):
copyraw
=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.
- =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.
Category: Excel :: Article: 551
