Excel - Compare two tables using COUNTIF

Do you have two tables (lists) in Excel, perhaps one is newer or you suspect it is different? You'd like to compare the two tables and see the differences easily? There are a few ways to do this, here I'm going to show how the COUNTIF function can be used, I've found it to be quick and easy to use.

Example
The following is an example where I have two tables in one Excel worksheet. You could have these tables in different worksheets of course. You also don't need to use Tables, this example would also work with ranges. It's just an example, I hope it is helpful to you:

The tables are named Table1 and Table2.

 

Table2 has less rows, only 1714 compared to 1718 in Table1. The question is, which rows are missing?

Enter a title to create a new column (above), a new table column will be created (below).

In the first cell in the new column you can enter the COUNTIF() function.

Enter:

=COUNTIF(Table2[Town], A3)

This means that we want look in the Town column of Table2 for whatever is in A3 (Table1). For example, it will look to see whether "Ampthill" is present in Table2 or not.

Because you entered the function into a column in a table, it'll replicate the formula all the way down. As you can see in the above example there is a result in each row of the column 'In Table2?'. COUNTIF() returns the number of times it found what it was looking for. Therefore you can see that on row 20 there's a zero, this means that "Ascot" could not be found in Table2.

Filter

Filters are automatically available for Tables, this makes it easy to see those results that are zero:

Filter the 'In Table2' column for all the zeros (where it did not find anything in Table2).

Enhance the look

For something quick the 0 or 1 is fine. If you want to share this with a others then you could tidy it up. Here's one way to do this:

Change the formula, add the surrounding IF() statement to set a Yes or No result. 

=IF(COUNTIF(Table2[Town], A3),"Yes", "NO!!!")

Having "NO!!!" makes it easier to spot where Table2 is missing this item.

Conclusion
This is just one way to compare data in two tables in Excel. It's nice and quick, I'm sure it can help anyone who just needs to quickly understand which rows are missing from a data set. 

I used the Excel 365 version as of August 2020 for this article. As the COUNTIF function is not new, I am sure this solution will work in older version of Excel too. That's another reasons it's a good choice.