# Compare Two Lists to Highlight Matched Records

Share
Embed
• Published on Apr 22, 2013
• www.computergaga.com/blog/compare-two-lists-to-highlight-matched-items/ Compare two lists in Excel to identify the matched records. Highlight the duplicate, or the unique records, by comparing two lists using the Match function and Conditional Formatting.
• Howto & Style

• MR ABALONE 18 days ago

Hi sir, really appreciate your sharing.
May i know, can the excel match the record and arrange them side by side?
thank you!

• Computergaga  17 days ago +1

Thank you. Sure. Instead of using MATCH alone. Combine it with INDEX, or use VLOOKUP instead. Use this formula in the column adjacent to the one you're searching from so that you get them side by side.
VLOOKUP can find the record and return information related to it. I have a video on VLOOKUP here - thexvid.com/video/EB-rhTyS3Fo/video.html

• Thank you mate.

• Verky Month ago +1

Does the formula change if I want to get the information on another tab? Do I add something saying to look at the data from the other tab?

• Computergaga  Month ago

Sure. You would add the sheet name. Using the formula from the video and imagining a sheet name of members the formula would be =ISNA(MATCH(\$A2,members!\$A\$2:\$A\$19,0))
If the sheet name had a space, so for example members 2019. Then the single quotes are needed like this =ISNA(MATCH(\$A2,'members 2019'!\$A\$2:\$A\$19,0))

• Danyos777 Month ago

Wonderful strategy! Thank you @Computergaga

• VFX Desi Month ago +1

Thank God for your *existence* !

• Kenneth Vela Month ago

It doesn't work in my sheet

• Salim Saba 2 months ago

When I watch your videos, I can keep each single step in mind easily. You make things easy to comprehend and your examples are stunning and useful. Thanks indeed Alan.

• Farid Albaloshi 2 months ago

Thank you Soo Much You MAke my Day ... and i was searching for this from one week ,,,

• gcNewd 3 months ago +1

Love this! thank you.
Would you know how to do this with currency, one sheet having negative amounts, one sheet same amount without the negative (the 2nd sheet is from a scan and export into excel and does not show negative)

• Computergaga  2 months ago

Thank you. What do you want to do? Check values that are not the same? The AB function will remove the sign from a number so that they can be compared equally.

• Deepak Bhardwaj 3 months ago +1

Ty for this vedio.... keep posting

• sathish kumar 4 months ago +1

it was very useful

• Solomon Kinyanjui 4 months ago +1

Hi Allan? I like this video, but how do I automate the copying and pasting of the unique records (in clour format) to a new worksheet

• Solomon Kinyanjui 4 months ago

The situation I'm referring to is for example where an employee data is in more than 50 columns and the total number of employees is almost five thousand. Now new employees are added in the list every month and old employees leave due to varied reasons. Now considering this is a huge data I would like the new employees extracted by copying and pasting in a new worksheet. The same should happen to those who have left but they must be in an independent worksheet.

• Solomon Kinyanjui 4 months ago

@Computergaga Thanks. I'm OK with VBA, well I thought there is a way in worksheet environment. Well for the case of macro recording what will happen in situations where you have different huge data with new conditions?

• Computergaga  4 months ago

Hi Solomon, if you are new to VBA I would record a macro of you filtering the green records and manually copy and pasting to another sheet. The generated code can then be improved to your liking depending on the exact scenario you desire. That will be a nice introduction to get an idea though.

• Waheed Khan 4 months ago

Sir it good but how we can remove the color from sheet

• Waheed Khan 4 months ago

Thank you very much

• Computergaga  4 months ago

Select the range, Conditional Formatting and then Clear Rules > Clear Rules from selected cells

• swizlestick 4 months ago +1

If this isnt working for you, make sure that you dont have any blank spaces in your cells.

• Francis Santora 5 months ago +1

Worked perfect, you're a genius! Saved me hours of drudgery.

• Jeremy Gaiser 5 months ago

Does it matter if the data is in a formatted table? im doing this exactly like you are with employees that have unique ID's and nothing happens.

• swizlestick 4 months ago

@Jeremy Gaiser There were blank spaces in some of the cells. Removed those and it worked.

• Jeremy Gaiser 5 months ago

and when i say exact. I am using a-d and F-I for my tables. Only difference is the range.

• Cynthia Szczepaniak 5 months ago +1

Thank you! Makes my job much easier. Will this formula work if the arrays being compared are on separate sheets or separate workbooks?

• Computergaga  5 months ago

• Cynthia Szczepaniak 5 months ago +1

I can live with separate sheets. You have shaved off about 3 hours of work today for me.

• Computergaga  5 months ago

You're welcome. Separate worksheets = yes. Separate workbooks = I don't think so, not with this method.

• Ubaid Ubaid 5 months ago +2

I appreciate it, really nice bro, i liked , i subscribe and i will watch all your videos thanks alot.

• Computergaga  5 months ago

You're very welcome Ubaid, thank you.

• Wanayn 6 months ago

Hi! would this work for several columns/lists? What I'm trying to do is to highlight the same strings in 10 different columns/lists with 100 rows each.

• Brian x 6 months ago

thanks for the video. my 2016 excel wont work. I put the exact the same formula and then, nothing happens. Everytime i go back to edit, i found it automatically add =" ......." at the beginning and end of the formula. Do you know why? thanks

• dimpy panwar 6 months ago

This was a lifesaver , I am not able to do the same for comparing 10 lists with each other. Can you please cover that too in one of your videos?

• Dolce Amore 6 months ago +1

what a life saver!!! thnx bruh

• Pune Pet Park 6 months ago

What would be the formula I would have to use to Highlight A2:A21 for names that are F2:F21=G2 (G2 in this case would be 'Devonshire")

• Computergaga  6 months ago

Select range A2:A21 the Conditional Formatting > New Rule > Use a formula to determine the cells to format. Enter = F2:F21=\$G\$2

• missXstreet 7 months ago +1

I'm not an excel experts.. this really helps me a lots. thanks mate

• SALEM Center 7 months ago

Hi my friend,
It says there's an error in the formula.
I'm using office 2013.

• SALEM Center 7 months ago

@Computergaga Yeah, you're right. Maybe because I'm using it on nouns instead of numbers.

• Computergaga  7 months ago

There could be multiple reasons for this. Check what you have against the formula in the video. It should work fine on 2013.

• jeny xOx 8 months ago

Thank you for this!!

• MOHD HAFIZ BIN MURNEH - 8 months ago +1

THANKS BRO

• Rohit Singh 8 months ago +2

Thanks Mr. GaGa

• Clarence Alger 8 months ago +1

I know this is old, but still let me say: thank you! Works perfectly as I wanted!

• Zola Clark 9 months ago +1

This is a great tutorial, thank you!! I was running into trouble using conditional formatting and one of the predetermined features. While I was trying to find matches between two columns I realized that the one column had multiples of the same number and was counting that as a match even though it wasnâ€™t in the other column. Your method solves that problem, correct?

• Computergaga  9 months ago

Correct. Using the formula instead of the in-built duplicate of Conditional Formatting it looks for a match in the other table and not the same column.

• Sajeev Kumar 9 months ago

HI i need to delete two same names, i use duplicate , but it remove only one name , i need to remove both names . Example ( boby, steve ,jeff, steve , evan ,eric ) need to delete both steve names . any formula please.

• Shane 9 months ago +1

Thanks buddy!

• Scott Bell 9 months ago +1

Sweet Jesus, I finally got this figured out thanks to you, bud. thanks!

• Mahesh Govind 10 months ago +1

Hi there, thanks for the video . Just want ask you how to do same thing if you have you data in 2 different sheet or workbook pls? Pls let me know .thanks.

• Computergaga  10 months ago

Thank you.
If the tables were on different worksheets you could use a formula like this, where Sheet2!\$A\$2:\$A\$42 is the data on the other sheet.
=ISNA(MATCH(\$A2,Sheet2!\$A\$2:\$A\$42,0))
I believe it works across workbooks also by adding the workbooks name like you would in a formula. However I would get it into one workbook as it is more reliable. Power Query is a good one to bring data from different workbooks into one in a quick and updatable way. CF can then be run from this.

• David Givens 10 months ago +1

2018 and this is still helpful. Thanks!

• VARUN DUGGAL,BAWU 10 months ago +1

Thank you sir

• Sidharrth Pillaii 10 months ago

Thanks for the video computer gaga. I have a request i have to find common names entries in two excel sheets 1 and sheet 2. I have done vlookup to get the common names, now i am facing the challenge that is sheet 1 has sheet one has name and sheet 2 has names with few more details. Now as i have got the common names by doing vlookup, now i need to add additional details along with the names from sheet 2. could you please help ?

• Computergaga  10 months ago

You can then use VLOOKUP to bring across these details. Look for the name from Sheet1 on Sheet2 and return information from the necessary column. Say for example, you have an ID for each name down column A of sheets 1 and 2. And you want detail that is in column C on sheet2. You could write;
=VLOOKUP(A2,Sheet2!\$A:\$F,3,False)

• Sreenivasa Rao Medida 10 months ago

How to add multiple columns in excel 2007

• Computergaga  10 months ago

You can add multiple columns in one go, as Excel will insert the same number of columns you select. For example, select columns C, D and E. Right click and insert Columns and you get 3 more columns.

• Ahsanul kabir 10 months ago +1

best method

• The Last Sifu 11 months ago +1

This helped me shave 15 minutes off of a daily task. Thank you!

• Manny R Year ago +1

Dude....... I blow everyone's mind that has a list that needs to be compared. I've gotten pretty creative with it as well. Saves me so much time!!! THANK YOU

• albatool social Year ago +1

This is what I am looking for, Thanks for tutorial video :)

• Joel Walker Year ago +1

Saved me a lot of time by not having to organize manually. Here is an example if anyone is trying to compare 2 columns with 2 other columns.
=ISNA(MATCH(\$A2&\$B2,\$AE\$2:\$AE\$40&\$AF\$2:\$AF\$40,0))

• Zakeer Hussain Year ago +1

This is exactly what I wanted.

• macmillan hanry Year ago

Dear Sir,
I am kindly requesting you to help me particularly in this section...... I have 2 Sheets in Book1.. Sheet1 has 6 Columns with data and 39 Rows With data and sheet2 has 6 Columns with data and 41 Rows with data. I want to find out differences one (Not Common) between these two Sheets Sheet1 and Sheet2.
Could you kindly Explain Formula In detailed way.. as early as possible
Sincerely Thanking you,
Basvaraj,

• macmillan hanry Year ago

Dear Sir,
Have you got Mail Sir?
Basavaraj

• macmillan hanry Year ago

Dear Sir,
Just Now I have sent a Excel File to Your Mail-ID.
Basavaraj

• Computergaga  Year ago

Sure mail it to me

• macmillan hanry Year ago

Dear Sir,
can I attach that Excel file Now to Your Mail ID? Easily... Fraction of second You can solve it.
can I Proceed.....? I am feeling that.... I am unable.
Willingly Thankful for all your Immediate Reply And It is my Gratitude to you.
Really It is Immense Pleasure for me and having wonderful conversation on this topic.
Thanking you Sir,
Basavaraj,

• Computergaga  Year ago

On your VLOOKUP. The A:F should be a single cell reference. So if the first record is in row 2 and the item you are looking for is in column A it should be A2. Copy that formula to the bottom and it will say #N/A if the item is on sheet 2 but not on sheet 1.

• Brigitte Meifeng Year ago

Thank you so much for this video. I have another question. Let say I have a record with 2 columns. ID and Title. The other record is ID and Url. How do I create an output with records that have matching ID, title and Url? Thanks.

• Computergaga  Year ago

Excellent work Brigitte.

• Brigitte Meifeng Year ago +1

Computergaga Got it to work. That is so awesome. Thanks!

• Brigitte Meifeng Year ago

Computergaga I will try that out. Many thanks!

• Computergaga  Year ago

You want to extract the uniques. So say you have a unique list of the ID and Title columns. Then use a VLOOKUP to fetch the URL for them.

• Brigitte Meifeng Year ago

Thank you but I have a question. Is there a way to extract the non duplicates? If I have thousands of rows, I don't want to scroll down to find the highlighted items and copy them one by one to separate them into another sheet? Thanks.

• Computergaga  Year ago

awesome isn't it

• Brigitte Meifeng Year ago +1

Computergaga -I didn't know that you can filter the column by color. Thanks.

• Computergaga  Year ago

once highlighted you can filter by colour. once filtered you can then copy these. That will save scrolling :)

• Carlos Alfonso Year ago +1

Thank you for this important lesson!

• B Prabhakaran Year ago +1

Very useful, Great Thanks!

• Isaiah Kronstad Year ago +1

pERFECT!

• Saurabh S Year ago +1

thank you so much, it was really helpful.

• Jitendra Shejwadkar Year ago +1

Thank you.

• urroyalhynus Year ago +1

Thank you!!!!!

• Fernand Carignan Year ago +1

Just what I was looking for! Thank you very much, it can't be clearer!!

• Marisol Jones Year ago +1

THANK YOU!!!!!!

• crtlyksds Year ago +1

very informative

• John Anderson Year ago +1

Thanks!

• mendebil Year ago

• Ravi Shingala Year ago +1

Loved this video even more because you taught it in two different ways. And of course, love the accent :)

• Welleh Kie Year ago +1

OH YOU WON'T BELIEVE HOW YOU SAVE ME!

• Computergaga  Year ago

Thanks for the comment Welleh, always good to hear nice feedback.

• Rajesh FJHS Year ago +1

Thank You Bro

• Zeeshan Ali Year ago

if we don;t have primary key but combination of first name. last name and city, how do we go about it

• Computergaga  Year ago

Not a problem, Zeeshan.

• Zeeshan Ali Year ago +1

Aah, I should have thought of that, thanks for your response!

• Computergaga  Year ago

You could create a new column containing the firstname, lastname and city. Then concatenate them in your lookup like in this video - thexvid.com/video/ZqWTsmk2Jk8/video.html

• Omoloye Awojobi Year ago +1

Thanks for your video - saved me hours of manually trawling through my spreadsheets

• Ly Spooner Year ago

This is really great. What I would also love to know is how to highlight the individual cells that might be different from the ones that match the row on the id, and also to add new records with a blank id, in order to maintain school/course membership lists.

• Kelsey Hamman 2 years ago +1

As everyone else stated, THANK YOU!! Huge help!

• Computergaga  2 years ago

Your welcome Kelsey. Thanks. Have an awesome day.

• Khan Khan 2 years ago

how to give numbers to highlighted cell in some other column ? ?

• Khan Khan 2 years ago

Thanks. But i figure it out. using sorting and then giving numbers.

• Computergaga  2 years ago

Not sure what you mean Khan. If a cell is highlighted then assign a value to it in a different cell? Sounds like you need a formula in that other cell. Maybe a IF function.

• Benia Munoz 2 years ago

What if in column A you had duplicate records. I tried the =match formula but it highlights all duplicate values in column A even though they aren't in column F OR it doesn't find any duplicates.

• Flavius Totorean 2 years ago

hello, i followed the instructions step by step, but for some reason it doesnt work. i'm using MS Office Profesional Plus 2010. Is there any condition regarding a previous formating of columns "a" and "f", as seen in your video?? Maybe they should be formated as number, or text, or ... I was trying the MATCH function, and it said that the formula contained an error. Any idea? Thanks

• Computergaga  2 years ago

Argh! Frustrated I didn't think of that. Some European countries use the semi colon as opposed to the comma in as an argument separator.
Good work Flavius.

• Flavius Totorean 2 years ago +1

finally! it works :) the problem was the separator inside the formula (its " ; " instead of " , " ). Thanks a lot! Now i can go home :)

• Computergaga  2 years ago

Hard to say without seeing it. You are quite right though, it is a good idea to check that the formats of the value you are looking for, and the list of values you are looking in are the same.

• Jen and Phils Wedding 2 years ago +7

you are a lifesaver!!!!!!!!!!

• Shivam Anshiv Goyal 2 years ago

Myself Shivam Goyal from Faridabad Haryana India, I have an problem in comparing two columns in a excel worksheet .I am working in this Worksheet for 6 Months approx , i have used or tried all the possible methods for comparing these columns but no desired results found. I have already applied or applied these methods (Vlookup , Conditional Formatting, Countif etc) but unable to find results. Please help
Shivam Goyal
Mob: 8447270294
Email: shivamgoyalvip1@gmail.com

• Tinisha B Year ago

What this ever resolved? I"m having the same issues.

• Computergaga  2 years ago

• Shivam Anshiv Goyal 2 years ago

Sir
Please provide me your email id so that i can send you the attachment , to prove that excel comparitive formulas fails on that sheet.
what i want to is, i have two lists and i want to find out which of the data in list B is also Present in A and Vice -Versa. Thats all..
Shivam

• Computergaga  2 years ago

How do you want to compare them Shivam. Are you looking for duplicates or uniques?
Send me an email and I'll try and help.

• Fredrik Jeanson 2 years ago

Hi Computergaga! I found this really helpful, I understand a lot more now, but I get an error after I click ok, where Excel thinks I'm not trying to write a function. I'm using Excel 2016, don't know if that changes anything... But has this happened to you before and if so how did you fix it?

• Computergaga  2 years ago

Legendary work Fredrik

• Fredrik Jeanson 2 years ago +1

edit: problem solved!

• Fredrik Jeanson 2 years ago

Thank you! My error disappeared, but I didn't get the exact result I wanted even though it was somewhat what I wanted, so I'll keep experimenting! But thank you for the help

• Computergaga  2 years ago +1

Excel 2016 should not cause any problems. You may need a semi colon ; in between the function arguments instead of a comma , if not that then I would check the brackets.

• Fredrik Jeanson 2 years ago

I should mention I did exactly as you did in the video,
the error/warning says:
There's a problem with this formula
Not trying to type a formula? Excel recognises things that start with (=) and (-) as formulas

• Ryan Klemm 2 years ago +1

• Rich Giles 2 years ago

Excellent, thank-you!

• Suryakant Tikare 2 years ago +1

This video is of great help for me.

• Megan Mooi 2 years ago

Hi... Great video! Thanks.
Quick question: What if I didn't want to find cells with the exact match but rather cells that "contained" or "included" the text?

• Computergaga  2 years ago

Hi Megan, thank you so much.
Assuming the text that you want to ensure was included is in cell A2 and your looking in range B2:B44 you could use the below. The * is a wildcard that replaces any text.
=MATCH("*"&A2&"*",\$B\$2:\$B\$44,0)
I hope this helps
Alan

• Cosmetic Dental Patients 2 years ago +1

Computergaga - Good job!!!

• mezoani 2 years ago +1

Thank you so much for the vid, it was a big help!

• Victoria Lee 2 years ago +1

Hi! Thank you so much for your response. But if the sheet has hundreds of entries is will be very time consuming to delete each manually. Request: can you teach us how to create such macro? Your teaching is very clear & concise & excellent. THANK YOU!

• Computergaga  2 years ago

On my list to do. Will produce a video tutorial soon.

With the technique in this video you can sort the list by colour prior to deleting to group them together.

• Victoria Lee 2 years ago +1

This is an excellent tutorial! Thank you. Are you able to deleted automatically those enteries that are non-duplicate? If so, can you teach us how?

• Computergaga  2 years ago

To do it automatically you would need a macro. Otherwise when the rows are highlighted in this tutorial, you can sort by colour and then delete the duplicates. That would take seconds, but it is not automatic.

• Odam TJ Ly 2 years ago

that was awesome
How can I count the total of match and no match?

• Computergaga  2 years ago

If the cells of the unmatched are empty you can use the COUNTBLANK function.

• Odam TJ Ly 2 years ago

sorry to bother but just one more question
how can you count if it not match? to count how many items does not match

• Computergaga  2 years ago

• Odam TJ Ly 2 years ago +1

Thx alot Computergaga you are the best for a starter like me

• Computergaga  2 years ago

Thanks Odam,
We would use a formula instead of Conditional Formatting. There are a few ways but this video uses COUNTIF to compare lists - thexvid.com/video/HUWrnLOOGsQ/video.html

You could then count the 1's to identify the number of matches.

• Reza Talebi 2 years ago

Thank you!

• L K 2 years ago +1

Awesome! You the man!

• Riaz mahmood 2 years ago

Hello,Is there a way to include in a new column and state that it is a "Match" or a "No Match"?

• Computergaga  2 years ago +1

Hi Riaz, yes using the example from the video, in a column I could use the formula below

=IF(ISNA(MATCH(A1,\$F\$1:\$F\$19,0)),"No Match","Match")

• Muhammad Yasir 2 years ago

There are 3 ways to Compare Excel Sheets or Workbooks.
1) 1st & the Best : Vlookp(The Most Powerfull Excel Tools).
2) Conditional Formatting,Make New Rules for Formating by using this(=A1Sheet2!A1)&Setting Format(In my Case i have sheet1 & sheet 2 for Comparison).
3)Match Function at Conditional formating...

• Star Lily 2 years ago

hi, i just tired it. But the whole culumn been highlighted, even if the number doesn't match, May I know the reason? Thank you.

• Computergaga  2 years ago

Sure, if you send me what you have I may be able to help

• Star Lily 2 years ago

Computergaga is any way I can show you in image?

• Computergaga  2 years ago

I need to see what is happening to know. There will be a reason.
Click Conditional Formatting and Manage Rules. There may be a conflicting rule.

• Star Lily 2 years ago

Yes, exactly the same procedure and formula, but it still appear that highlighted all cells instead of the Match ONE.

• Computergaga  2 years ago

I would double check the formula and the table layout. Is it the same as mine? The cell references may need changing.

• mirin jawbro 2 years ago

I need to match a table with criteria of plus or minus 10 percent (this is for an OTIF report) any ideas?

• Rune Tonseth 2 years ago

HI Nice Video. But it is f....frustrating. Im trying to compare and match two culoms to save time. But everytime I do this I get a error message. The Forumula automaticly get a + sign: =match(\$B2+,\$D\$2:\$D\$9,0) I remove that and I get an ERROR message. " We Found a problem with this formula. Try Clicking Insert function on the forumla tab to fix it.... bla bla bla bla
What am I doing wrong I follow all the steps..... I havent saved time I have to do it manually...:) But I wont give up

• Computergaga  2 years ago

Ha Ha, Job done though Rune

• Rune Tonseth 2 years ago

Hi Do you have an email adress? I can send you a printscreen. Not from the document but Im trying to learn this thing so im testing it on just a test sheet

• Computergaga  2 years ago

Strange. You sound well equipped to be able to solve it. If you use VLOOKUP, this formula does not stand a chance.

Drop me a message if you need extra help.

• Rune Tonseth 2 years ago

Im familiar with Excel I use it everyday. Im used to create vlookup etc but have never used the match function.

• Rune Tonseth 2 years ago

HI I delete the + sign and hit enter. But get the alert commented. I might restart the machine.

• dollno5 2 years ago

Ok, this is the third time I've checked out this video. I've solved my excel problem, but do you know that you sound just like Jason Statham? Nice.

• Computergaga  2 years ago

Awesome, Jason Statham wishes he knew this Excel stuff :)

• Asifgraphy com 2 years ago +2

• Niloy Sadat 2 years ago

really helpful thanks man, tomorrow i will check with my datas

• Ashwat Nagarajan 2 years ago

i really liked this video, thank you :)

• Ran Shir 2 years ago

You the man, thanks!

• Robert jirsaraie 3 years ago

• Jagu Kulkarani 3 years ago

SIR PLEASE HELP ME I WILL UPLOAD ONE EXCEL FILE AS U SAID I DID EXACTLY BUT IT IS NOT WORKING PLZ GIVE EMAIL ID

• Caz M 3 years ago

thank you! i had to bump up two data sets of students looking for duplicate records today so i could adjust funding for students on both lists. by using the match function i was able to locate the dupes in a spreadsheet that had about 3000 line items very quickly!

• Justin Howell 3 years ago

This really helped me! Thank you so much for posting this video!

• Mike Rice 3 years ago

Great tutorial. How can i get this function to recognize text selected from a drop down list on a separate sheet?

• Tanner Tucker 3 years ago

I guess one can use two conditional formatting formulas: one to find the match, and a second to find the unmatched rows?

• Tanner Tucker 3 years ago

+Computergaga Thank you. I though it would be "nice" to shade matches with one color and non-matches with a different color.

• Computergaga  3 years ago

+Tanner Tucker Absolutely! You can have unlimited Conditional Formatting rules per cell. The NOT function can be used to reverse the criteria.

• Tanner Tucker 3 years ago +1

Thank you, this is excellent!!

• jazzista1967 3 years ago

greattt!!! that's what I needed..