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.

Lists

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.

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

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.

20 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.

    Ros

  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.

    Cheers,
    Valerie

  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:

    THAT IS VERY USEFUL WHAT CHALLENGED ME WAS IF THE SPELLING OR SPECIAL ABBREVIATIONS EXIST IN THE LIST EVEN THOUGH THEY ARE EXACT MATCH EXCEL DIDN'T MATCH THEM BECAUSE THEY ARE NOT EXACT.HOW CAN I SOLVE IT.

  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.

    Thanks!

    • 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:

      =COUNTIF(Sheet2!$B:$B,$A2)

      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:

    Hello,

    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

  11. Mahdiyar says:

    It was great ; now I have another question:I have a table with two columns first the name of cars ; and next the number of that cars . Now I have a similar table with 2 column and I want to search it in the first table.

  12. abc says:

    On sheet 1, I have a list of numbers in column AX

    On sheet 2 I have I have a list of numbers in column A and I have a list of names in column B

    If I want to see which numbers are in column AX Sheet 1 match column A in Sheet 2 - Id also like my formula to let me know which names those account numbers belong to (from column B) so instead of true or false Id like the name to appear.

    How can that be done?

    I.E if column AX sheet 1 has a number that matches Column A sheet 2 it will return with the name from Column B sheet 2

  13. Jonathan says:

    This was incredibly helpful! Thank you!

  14. Georgina Ram says:

    Is there a way to do this with two worksheets instead of columns in the same worksheet?
    I find myself going through each entry looking for the property ID in one worksheet to highlight it in the second worksheet, that way I know what properties are already registered with the City. However, this takes too much time. VLOOKUP will not work in this instance because it only returns one item that matches.
    In my situation, I am looking for a function that will return (highlight) all of the matching property IDs in sheet 2 using the entries in sheet 1.
    Any help would be greatly appreciated.

Leave a Reply

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