Excel Checking List from Another List

---

2020-02-19

This is similar to my last blog post where I explained how to filter a list in Excel based on another list. This time, the task is more straightforward - we just want to identify if an item in one list is present in another list. I find I use this technique quite often when using Excel (especially during data migrations).

Let's look at an example of the planets in the Solar System. We'll list out a list of Solar System objects in Column A, and we'll list out all the Solar System planets in Column D. Finally, we'll use Column B to identify if the object is a planet or not:

To indicate whether an object is a planet or not, we'll compare the name in each cell of Column A against all the names in Column D. If there's a match, then we have a planet. To do this, we'll enter the following formulate in Cell B2:

=IF(ISNA(VLOOKUP(A2,D:D,1,0)),"No","Yes")

In the end, this will return a "Yes" if there's a match, or it will return a "No" if there is no match. Here's how the formula works:

• IF is a statement that examines a condition in the format: IF(condition, true, false). In the above formula, the condition is ISNA(VLOOKUP(A2,D:D,1,0)) and the true action is "No" and the false action is "Yes". This may seem backwards, but please read on.
• ISNA is a formula that means "Is Not Applicable". So, we are looking at something and if it is not applicable, it will return true. If it is applicable, it will return false (making more sense now, right?).
• VLOOKUP is a function that looks up data in other columns with the following format: VLOOKUP(source, destination, true, false). In our case, the source is A2 (Mercury), the destination is Column D (all the names), it will return 1 (true) if found, and it will return 0 (false) if not found). Since Mercury is listed in Column D (the list of planets), this function is going to return 1 (true). Since this is true, it is "applicable" so ISNA is going to return false. Thus a "Yes" will appear in Cell B2.

Once you see it working in Cell B2, simply copy (fill down) the formula into the other cells in Column B. You should wind up with this:

And as you can see, one of our objects is not a planet, and the others are.