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.

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

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

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

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 🙂

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.

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.

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.

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.

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.

Very useful function. Thanks a lot!

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!

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.

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

=COUNTIF(Sheet2!A:D,Sheet1!D2)

i am seeing '0' being returned and when i pull down teh formula i am seeing '0' and '1'. I am trying to return the exact matches...the exact names...text field

Further to teh initial comment....I understand that it will show a value of 1 in the matching row of the value that exist. Thanks for the help... However, is there a way in a separate sheet that i can see list of all names that matches

=IF(COUNTIF(Sheet2!A:D,Sheet1!D2)=1,Sheet1!D2,"")

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.

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

This was incredibly helpful! Thank you!