Or, how to find values in a list that don’t appear in another list.
Let’s take a look.
In this worksheet, on the left, I have a list of 20 names.
On the right, I have a much larger list of over 1000 names.
How can I quickly figure out which names in the smaller list also appear in the bigger list?
Well, there are several ways that we can approach this with formulas in Excel.
We could, for example, use theVLOOKUP functionor theMATCH functionto find exact matches.
But another powerful and simple option is theCOUNTIF function.
The range is simply the list of names in the large list.
I need to lock this reference so it won’t change when the formula is copied.
For criteria, I simply use the name in the current row.
When I copy this formula down, we get a count for each name.
A “1” means the name appears once in the larger list.
A “0” means the name was not found.
Now let’s reverse the logiclet’s count names that don’t appear in the big list.
If the count is “0”, return “1”.
Otherwise, return “0”.
This effectively “flips” the results.
This problem really shows off the flexibility and utility of theCOUNTIF function.
It’s also a good example of how to nest one formula inside another to extend or change behavior.
