Compare two lists in two different worksheets and highlight the differences between them

I have two lists on two different worksheets.

Old list is the previous list of names and the New list is an updated list of names.  I want to be able to see what has changed between the two worksheet lists.

I have tried to use COUNTIF but not sure if I am using it correctly because it shows the entire new list as not existing in the old list.

Could someone guide me the right direction?

Sonia BowditchInformation Security OfficerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shaun VermaakTechnical SpecialistCommented:
=IF(ISNA(VLOOKUP(A1,Sheet1!A:A,1,FALSE)),"Does not exist","Exists")

Open in new window

To compare changes do this and change 1 to whatever column

Open in new window

Sonia BowditchInformation Security OfficerAuthor Commented:
I have tried VLOOKUP but it keeps coming back as none of the name as existing in the old list.  I have tried TRIM but no luck.
The lists will keep changing with names being added or delete from each list.
Sonia BowditchInformation Security OfficerAuthor Commented:
Vlookup may not work for what I have in mind.  Probably the way to go would be find and match functions or similar.
NorieAnalyst Assistant Commented:

If you simply want to find if values in one list are in another you can use MATCH.

For example, let's say you have a list in column A on Sheet1 and a list in column A on Sheet2.

You could put this formula in column B on Sheet1,

=IF(ISERROR(MATCH(A1, Sheet2!A:A, 0)), "Not i", "I") & "n list on Sheet2"

and it will tell you which values on the list in Sheet1 appear in the list in Sheet2.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.