Challenge: find out which entries in one list appear in another list.
This problem comes up time and time again in Excel. In fact, it's probably the most common challenge that people come to me to solve. Here's how to solve it.
Let's say we have a list of car manufacturers in column A. And a list of US presidents in column D.
And in column B, we want to flag whether the car make appears in the list of US presidents. Enter the following formula in cell B2.
This will return "Yes" for rows 2 and 4, as Ford and Lincoln both appear in the list of US presidents. And it will return a "No". for Chrysler and Toyota in rows 3 and 5.
The formula can then be copied down the full length of the first list to perform similar checks for the other car makes.
Here's an explanation of the formula, from the inside out.
- VLOOKUP(A2,D:D,1,0): this looks up A2 (Ford) in column D (the list of US presidents), and brings back the data from the first column in the look-up range—or in other words, it will bring back "Ford". If the lookup fails, then it will bring back #N/A (meaning "not applicable")
- ISNA(VLOOKUP(A2,D:D,1,0)): the ISNA checks whether the VLOOKUP has resulted in a "not applicable" answer. So for the rows for Ford and Lincoln, it will bring back the value "FALSE" (i.e. it brought back a value as opposed to a "not applicable"); and for the other rows, it will bring back the value "TRUE"
- IF(ISNA(VLOOKUP(A2,D:D,1,0)),"No","Yes"): finally, we wrap the check in an IF statement. If it's true, then the answer is "No"—i.e. the entry is not in the list; if it's false, the answer is "Yes".
Hope that makes sense. Let me know if you need any help with this.