Check which entries of one list appear in another list

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.

This entry was posted in How to. Bookmark the permalink.

17 Responses to Check which entries of one list appear in another list

  1. Rosario says:

    Thank you for explaining it in such an easy and understandable way!
    Been searching high and low for this in other sites, and being a dummy as I am, couldnt figure out how this worked... until i visited this website!

    Thanks again.


  2. Valerie says:

    This is great Dan! Have been looking for this to cross check my list of contacts against conference lists, so that I can set up appointments with the right folks! If you also have a tip on how to highlight the "hits" on the lists you're comparing, that would be of even more value to me.


  3. Lynne says:

    Any tips on doing this for huge lists? I have two enormous lists of customers (over 1M rows each) and would like to see which customers are on both lists. Would prefer not to do it this way for each row 🙂

    • Dan says:

      Do you mean the lists are too big for Excel, Lynne? If they can fit in Excel, then the COUNTIF function should work fine. If not, then you'd need to do something in Access or another database package. Sorry for the delay in responding.

  4. Zane says:

    Where did you obtain the information to write Check which entries of one list appear in another list ?

    I think I have actually reviewed the same info somewhere.

  5. DERESSE says:


  6. Cindy says:

    I would like to do this, but where the list is part of the formula, not in cells. I want to put it in a macro that sorts rows based on whether or not a cell is in the list. I'm using this for files that are temporary, so I don't want to go to the bother of entering the list into cells each time.

  7. Liz says:

    Thanks for the easily understandable solution. My lookup tables were on different spreadsheets, one for each year, and your formula (adjusted for the lookup area) worked perfectly.

  8. Carlos says:

    Very useful function. Thanks a lot!

  9. James says:

    Dan, how would I do the following:

    I have a list of 300 vending machines that went down last year (2015). How can I filter these 300 machines from a performance master list of 54000 machines? I want to track the performance of the 300.


    • Dan says:

      Let's assume your master list is on Sheet1 with the machine ID in column A. Titles are in row 1. And your 300 machines are in Sheet2 with the machine ID in column A.

      Insert a column at column B in your master. Put a column title in B1 of "Broke in 2015".

      In cell B2, you need the following formula:


      Copy that down the master.

      Anything with a 1 in that column broke. Anything with a 0 did not. Filter on that column.

  10. RRS says:


    How can this be done if we want to see if a value in one sheet appears anywhere in another sheet. That is a value in Sheet 1 column D can appear in Sheet 2 in either columns A,B,C,D

Leave a Reply

Your email address will not be published. Required fields are marked *