Trouble with formulae? Lookups not going so well? The Wizard of Excel can help. Dan Harrison offers practical tips and examples, as well as Excel training and consulting.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.
I had a similar question but googled IT, I think Dan left out a step he considered too obvious:D to copy the formula down the list just hover over the bottom right corner of the cell which contains the formula and double click it. Make sure the longer list is on the left. Thanks much Dan, this is a lifesaver.
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.
One technique I've used for corrupted lists is to sort the list on the column in question, which brings up close spellings together, then put in the correct spelling at the top of the range of close spellings and drag it down to include all records in question, which makes them all exactly the same.
Any list that requires an exact spelling should use a drop down list function for input. This forces the user to use the correct spelling and ensures the integrity of the list data.
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.
Nothing. I have a sheet of items that sold and a master list of all items. I am trying to mark them in some way to prepare for a new master.
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!
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.
Nice, easy to walk through, keep the geniuses
Perfect!!!
Hi, I have 1 doubt. Imagine i qam working in sheet3 and i want to lookup value for cell b1 in sheet1, sheet2 and sheet4. what is the formula...for one sheet lookup, i used the following formula...
=(VLOOKUP(B1,Sheet1!B1:E10,4,FALSE)....in this iwant to search for multible sheets...help me
I have a master list in Sheet 1 column A and I need to see which ones are matched in Sheet 2 Column A. Would like to know how to complete this. Thanks!
How can I check whether a combination of information from two or more cells in one dataset match the combination of information from another dataset? For example: I want to compare both the name and the email address of each entry in a dataset against another dataset.
I'd like Excel to tell me if the name/email combination doesn't appear in the other dataset. As a result, I would know if someone was added to the set (and so is present on one set but not the other) OR there's a discrepancy in someone's email address (thereby letting me know that I need to double check which email is accurate.) I understand I can get around this dilemma by combining the name and email cells into one cell and then using the command from above. However, that's a clumsy approach. I am betting you have a much more elegant method! 🙂
Just wanted to say, this is so easy to understand the way you have described it and explained.
It has saved me countless hours of manual checking and search for 100's of rows out of possible 10s, 100s thousands:)
thank you.
Thanks, very helpful. I was able to accomplish an important task using the function.
Instead of entering "Yes" if the value in the first list appears in the second list, how would you enter the value that appears in same row but a different column of the second list?
=IF(ISNA(VLOOKUP(A2,D:D,1,0)),"No",VLOOKUP(A2,D:H,5,0))
That will bring back column 5 of your range (i.e. column H, given that the range starts in column D).
Here is the issue I have ran into. I am using this formula =IF(ISNA(VLOOKUP(B396,B2:DB394,1,0)),"No","Yes") and ass I click the small black box to drag it town to cover multiple cells it changes the formula as shown below...
=IF(ISNA(VLOOKUP(B396,B2:B394,1,0)),"No","Yes")
=IF(ISNA(VLOOKUP(B397,B3:B395,1,0)),"No","Yes")
=IF(ISNA(VLOOKUP(B398,B4:B396,1,0)),"No","Yes")
=IF(ISNA(VLOOKUP(B399,B5:B397,1,0)),"No","Yes")
How do I drag or copy this formula without the numbers auto updating.
Here's what my list is for: I teach CPR and the certification is good for 2 years. I am trying to create a formula that tells me if the persons name appears as a previous student when they sign in.
Thanks!
Garrett
If you put a dollar symbol ($) before the letter, then it will hold the column when you copy and paste around. If you put a doolar before the number, it'll hold the row steady.
This is the most helpful Excel formula that I have ever discovered. Basically thanks so much for saving my life on a day to day basis.
What if the presidents first and last name were in the same column? Is there away to do this and have it search for the cells that contain "ford"?
What if the presidents first and last name were in the same cell? Is there a way to have it check for cells that contain part of the word? So if you searched for Ford it would output the whole cell contents i.e Harrison Ford?
Hi Dan,
I have a question. What if I want to do this, but some of my cells contain several items that may be in the list. For example, if my list was:
A
B
C
and my cell contained: A,B ; how would I get that to come up? Thanks!
The first time i test ran this on a small amount of data, it seemed to work. I dragged the formula down column B.
The second time it is bringing back wrong answers.
IE: "No" to numbers that it is easy to see in the second column
Never mind, i found the issue LOL
Hi Dan
How to retrieve information from another sheet by name with latest 2 date?
Also how to cross over from one year to another does it still retrieve latest date?
Column A1 = Name appears 20 times
Column B1 = Dates appears 100 times
A6 = 1/1/2019 MONTH/DATE/YEAR
A100000 = 5/27/2020
Column C1 = Distance & so on to Column N1 = Course
=VLOOKUP(A6,'QLD RESULTS'!A:A,1,FALSE)
Sorry screen freeze
Regards
Tony