Excel - Find what is missing?

I have an Excel file "Country.xls".

This Excel file contains two sheet/reco开发者_开发问答rds "Country" and "Country_Old" Both Country and Country_Code sheet/record has the following item "Code" and "Name"

I want to find all Country_Old.Code match in Country.Code and mark all matching records with a color.

How do I do that?

I'd use the MATCH() function, in conjunction with conditional formatting.

MATCH() looks for a reference value, within a reference range, and returns it's position if there is a match, and #NA if there isn't. You can specify the match type, you'd want 0 (exact match only).

I would make both your data sets into Tables (Insert --> Table), so you can use the tidier table syntax. Give your tables sensible names (under the Table Tools ribbon) and your formula would look something like this:

=ISNUMBER(MATCH([Code],Countries[Code],0))

The [] syntax is a result of using tables, what we're basically doing is looking for the current row's Code value in the Countries table's Code column, for an exact match, and looking at whether you get back a number (yes there is a match) or not (no there isn't). That'll give you a column of TRUE or FALSE.

If you want highlighting in different colours, you then want to apply Conditional Formatting. Note that you could equally filter on the matched column to just show the FALSE results! Simplest way to do this is just to highlight the matched column, just select the whole column and go to Home tab --> Conditional Formatting --> Highlight Cell Rules --> Equal To and choose a colour for the values TRUE and again for FALSE.

That will highlight just your matched column, if you wanted to highlight the whole row, you need to create a rule manually, using a reference to just the column you want to test for each cell. If you want details on that, ask as a separate question, as this answer is getting long already!