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:
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.
My name is Rick Towns and I am an amateur astronomer and computer programmer from Canada. This is a collection of interesting posts I've gathered over the years.