Google Sheets - Compare Two Lists for Matches or Differences

Learn how to identify repeated values in your spreadsheet table. We discuss a method using conditional formatting and one using an IF function.
Go here for the formula. KZbin wouldn't allow it in the description!! bit.ly/sheets-dupes
Timestamps:
0:00 Intro
0:23 Conditional Formatting
3:06 IF Function
Learn more from Prolific Oaktree:
🌳 Next Video - Google Sheets | Sort and Filter by Color • Google Sheets - Sort a...
🌳 Related Playlist - Google Apps | Full Tutorials - bit.ly/google-tutorials
#googlehseets #spreadhseet #conditionalformatting

[@PianoWithJonny]

Thank you SO MUCH!! I've looked through so many tutorials on KZbin trying to figure this out and no one could explain it as simply as you did. Thank you!!

[@ProlificOaktree]

I glad it helped out.

[@thomasfox4513]

Thank you for such a quick, clear instruction. The time invested in your video and building the formula has saved me hours already.

[@kaiawang9593]

I've watched multiple videos and read articles on this problem and yours is the easiest to follow! Thank you so much!! Wondering how to organize all the highlighted cells in one column... Again thank you!

[@World29283]

This just saved me like 4 hours of comparing thank you so much!

[@ProlificOaktree]

Awesome, glad it helped.

[@annczeponis1989]

This was so amazing - trying to do attendance for an entire school for our new remote learning due to school closures. What a lifesaver - stay well.

[@ProlificOaktree]

Cool, glad it helped! Us accountants get to stay inside so all is good here.

[@GunHudson]

Amazing tutorial. I could listen to your voice all day, your mic + voice + your explaining style is one of the best tutorial voices I've heard. :)

[@ProlificOaktree]

Glad you liked it!

[@austiningram3860]

Awesome, clear, visual. Thank you very much Professor Oak!

[@ProlificOaktree]

You're quite welcome my pupil.

[@teddypcadieux8271]

This is amazing!! Thanks so much, saved my life!

[@tomcha75]

Thank you for the short and sweet tutorial. Exactly the function I was looking for.

[@ProlificOaktree]

Great to hear!

[@nicolemasureik6783]

That was THE MOST HELPFUL video I have seen on using custom formula to identify duplicates! Thanks!

[@ProlificOaktree]

Thanks, glad you liked it!

[@joycefamilara2428]

LIFESAVER! I could almost cry. Every other solution was so complicated compared to yours. Thank you!

[@ProlificOaktree]

You're welcome!

[@alphabeta01]

Thanks so much, finally found a match to my requirements. Tried earlier with IFS, OR but MATCH functions seems so much better. Thank you!

[@ProlificOaktree]

Great to hear!

[@Geekboarder]

Just what I was looking for, thanks !

[@anabiapassaretti93]

That's EXACTLY what I needed! Thanks a lot!

[@Victor-bn4yb]

Thank you, that helped me a lot.

[@SylvioMedeiros]

Looks like every 2 months or so i have to go back to this video, its so good. Save me hours of work in less than 3 minutes

[@ProlificOaktree]

Well, we're always happy to have you back.

[@nitinanand4732]

This was super helpful and concise. Thank you!

[@ProlificOaktree]

You're welcome!

[@richardnau2672]

This was so helpful! Thank you for the tutorial.

[@emericpepin3527]

Thank you ! Exactly what i was searching for :)

[@mariapeagler2667]

You rock! I didn't think this would be possible. Thanks so much for the help!

[@ProlificOaktree]

Happy spreadsheeting to you Maria;)

[@hussainabbas4012]

Super helpful. Thank you!

[@likewhaaaat]

Thank you SO much! This was EXACTLY what I was looking for! Seriously appreciate it!

[@ProlificOaktree]

I’m so glad it helped!

[@brandon.brooks]

Just what I was looking for! Thanks!

[@ProlificOaktree]

Glad I could help!

[@KudretBayram]

Great explanation, thank you

[@TAP6583]

Yep, exactly what I was looking for and thanks for using simple data for the demostration

[@ProlificOaktree]

Glad it was helpful!

[@NikhilKumar-zg4lt]

This is very helpful dude, thanks a ton

[@haielle1678]

Very useful! thank you for sharing!

[@pedroestima]

Just saved hours of manual work. Thank you very much!!!!

[@ProlificOaktree]

Great to hear!

[@matthewrowlands7271]

I watched about 347 tutorials trying to find how to do this. Brilliant thanks

[@ProlificOaktree]

You're welcome. Glad it wasn't 348. That would have been _really_ bad.

[@bewatter3014]

Bro i appreciate it!!! For people who dont know if you have values in your sheet you have to first format it into numbers. Thats why i kinda lost to much time on my work LOL

[@frogboy831]

this was so cool. thank you!

[@yoannafranciscamarita8056]

This is super helpful!! THANK YOU SO MUCH!

[@ProlificOaktree]

You're so welcome!

[@n3oninja]

You rock! worked great!

[@ProlificOaktree]

Excellent! Party on.

[@AliRaza-je9ir]

Thanks for such informative video

[@hlao7057]

legend! works perfectly

[@rachelcruze9232]

This was very very helpful. Best explanation I came across!

[@ProlificOaktree]

That's a great compliment! Thank you for taking the time to let me know.

[@rachelcruze9232]

Hi! Thank you. I have another question. I am trying to find how to select the hilighted information and create a list based off of that. Do you have a video that explains this?

[@davideckhardt3164]

How could you select the matching values and add just those values to a new column?

[@charalamposkounes5071]

Thanks, EXCELLENT VIDEO!

[@ProlificOaktree]

Thanks, I'm glad you liked it!

[@Terry2011000]

Thanks! No more manual comparing.

[@333canyol777]

Thank you for your video! Is there any way to compare more than 2 columns? I have 4 columns in which numbers should match. If all four numbers are equal, it's true otherwise (if at least 1 out of 4 do not match) is false. Ideally to make it with conditional formatting to color in green if true and in red if false.

[@YagoStecher]

SAVED MY LIFE

[@meganewing6084]

GREAT tutorial and ALMOST exactly what I needed. Can I do this same thing on two different pages in the same sheet?

[@Nevir202]

Yes you can, but to do conditional formatting between sheets, you have to use INDIRECT there is a tutorial on how to handle that on YT.

[@piratediffusion]

works! you're a genius thank you so much

[@ProlificOaktree]

Glad it helped

[@holguiniable]

Thanks, i try find vídeos in spanish but thé only vidéo that know do this action its yours,, you are great

[@ProlificOaktree]

You are welcome!

[@user-wu7kf9bv8s]

Thanks for information and working method

[@ProlificOaktree]

Welcome

[@marine09666]

Thanks for this

[@ProlificOaktree]

You're welcome

[@AshleyRigsbee]

So helpful thank you!

[@ProlificOaktree]

You're so welcome!

[@chrissmart7639]

a thousand times thank you!!!!

[@ProlificOaktree]

Thousand times welcome

[@ipeiep5046]

Thank you so much!

[@ProlificOaktree]

You're welcome!

[@kasyanof]

Thank you very much!

[@ProlificOaktree]

You're welcome!

[@MattVolpert]

I think I found a typo in your description formula. In the video you write "=MATCH(D2,B$2:B$8,0)" but in the description, it's listed as "=MATCH(D2,B$2:A$8,0)"

[@ProlificOaktree]

Wow! 63,000 views and you are the first to point that out. I think you're right so I updated it. Thanks again.

[@iiilona]

Thank you! It works with semicolons for me. Russia

[@georgel4730]

Very helpful video. Is there any chance you can highlight a duplicate cell between multiple sheets? Thanks

[@ProlificOaktree]

Try adding the worksheet name to the cell reference. See here for more info on how to construct cell references to other worksheets kzbin.info/www/bejne/rHOoi5yJdplsg68

[@JanakRajani]

If you want to use "match" for conditional formatting based on a column in another Google sheet - this video's approach will not work (why Google Sheets, why have you not fixed this!) I have found this workaround works really well as the conditional formatting formula: =match(K:K,indirect("AnotherSheet!A:A"),0) Hope the "indirect" function (within "match") helps you despite this Google Sheets limitation.

[@mohsha6797]

tnx it helped.

[@amiryusufbinmohdhaizadupm9981]

THANK YOUUU, can finally save time when checking the student's attendanceee

[@ProlificOaktree]

Glad it's helpful to you!

[@platonmileev546]

Hi, thanks a lot for the video. Is there a chance, when you can higlight not only one cell, but full row?

[@ProlificOaktree]

Yes, take a look at this video kzbin.info/www/bejne/jWjam4t9r6dnntU

[@nicholasnavarini7067]

Can you also add a feature where you hover over a cell and see its specific value in another cell. Instead of doing a "control F" you simply hover over the cell and see where it's highlighted in the other column?

[@boismagplay7155]

Hi Thanks SO MUCH ! Just one question : How can I copy only the email in color ?

[@ProlificOaktree]

The color doesn't exist in the cell so it can't be copied.

[@DiaborMagics]

I know this is not the same, but do you know if there is a way to compare 2 lists that have equal values in them and return numbers that indicate how much they changed? Example: there's a track list of x songs. Column A has the 'old order', column B has the 'new order' after a new round of voting. The columns are sorted with the highest voted song at the top and lowest at the bottom. How can I make Column C tell me which tracks remained in the same place (value 0), which ones rose 1, 2, y places (+1, +2, +y) or fell 1, 2, z places (-1, -2, -z). I can't really figure it out.

[@romaknafel4116]

Hey! Please tell us how to import those matching keywords into new excel

[@MintOrange]

Thanks boi

[@jlederer9831]

I love you more than I can ever, ever, ever say.

[@ProlificOaktree]

Love you too.

[@learnwithgna6561]

I want to highlighted two columns then what will be the formula

[@djnc4588]

Hi. I have enjoyed and learn a lot from your videos. But I have searched through but couldn't find a video matching what I am looking for and currently am struggling to find the correct query for what I need. I understand how the MATCH, FILTER is. However, if I have 2 lists, Column A with Monday, Tuesday, Wesdnesday, Column B with Tuesday, Wednesday. My results should be of those not in Column B, which is Monday.

[@ProlificOaktree]

You could use VLOOKUP and whenever it doesn't get a match, you've got what you want.

[@angogar]

Hi. we play with the workmates who tip the most rows on the Europatipset (Sweden; series of 13 matches). And now I have created a spreadsheet about who has how many right. Everything works until we have some with the same number right. I have a formula that finds the most number right and writes in the cell next to the winner's name. But if they are two or more? how to write all the names that are right?

[@ProlificOaktree]

Just feeling a little crazy here...just output a list of the winners with the SORTN function with the option to show ties. BAM, done! sheetshelp.com/filter/sortn

[@user-gg2qt1or7c]

This formula is good for accurate matches, but what if i need to find cells having just part of word. From your example, i will typin only 'yell' or 'ang' in B column, and it have to light up 'yellow' and 'orange' in D column. Is there the way to do this and how?

[@ProlificOaktree]

No, but I am guessing that would get complicated pretty quickly. How much of a match? Would typing "y" match to yellow? You may be able to string together a formula with different versions of part of the word, but I don't know a good way to get this done.

[@saraflores338]

Could you show an example of doing this exact same thing but the data sets are in different tabs within the same google sheet?

[@ProlificOaktree]

Take a look here for that type of cell reference: sheetshelp.com/cell-references/#References_To_Another_Sheet

[@alexshevch]

Hi, how to modify formula to match 2 arrays which are on different tabs? When I write =match(G2,Relevant!G$2:G,0) - error appears. is it possible?

[@mariapeagler2667]

Thanks!

[@ProlificOaktree]

Oh yeah, this makes my day! Thank you.

[@mariapeagler2667]

No one understands what goes into these videos except fellow creators. Happy to support great videos and creators behind them.

[@dimitrikorsakov2570]

Can I use the MATCH function across sheets? So check a range in sheet 1 against a range in sheet 2?

[@ProlificOaktree]

Yeah, see here for how to reference across sheets. kzbin.info/www/bejne/rHOoi5yJdplsg68

[@ozzyfavela1]

Is this formula I would use to identify and remove duplicate records and emails? I have a very very large list with company name, first, last name, address phone email etc.. and some I'm sure are even there up to 10 to 15 times, but they are scattered AND not all all of those 10 to 15 records have EXACT data, meaning...one may not have the zip code, another may have the zip, but not the last name. They all DO have the emails though. Once identified, how can I do mass remova/delete? Any kind of guidance is very much appreciated. Thank you sir.

[@ProlificOaktree]

Maybe use the UNIQUE function no the emails and then pull the other columns back in with VLOOKUP.

[@asherlbryant]

I was too looking for the answer to this question. Here I found the answer: productforums.google.com/forum/#!msg/docs/HCOCOS7A-w8/Jb41fsjVBgAJ

[@vijayaaditya3869]

This is awesome. How do you even know all these? :D

[@ProlificOaktree]

When you think about video topics for hours, you tend to learn them!

[@waterdamagerestoration4490]

I want to do almost the same, but my list of words is in just 1 column. So in the case you are working on, how do you find the duplicates in the D column (and get the spreadsheet to highlight the duplicate words)?? Thanks,

[@ProlificOaktree]

I have a video on doing this in one column right here: kzbin.info/www/bejne/eaPad4Snrd-EpJo

[@abbasalyasere]

When I transfer data from one file in Google Sheet to another file with (= QUERY (IMPORTRANGE), the row links do not move to the new rows?

[@bewatter3014]

Hey, is there a possibility to to compare list A to list B and mark in list A the once which arent in list B and to get a new list with the once highlighted only?

[@ProlificOaktree]

That sounds like a custom action that you'd have to write in Apps script.

[@celiatapetillo9305]

Who I add conditional formatting that will change cell colors if there are not empty based on a certain cell?

[@lotusvines3698]

Facing issue with this ISNA function, even though value is same while comparing two columns but its highlighting it as unique. Can you clarify. I can email you screenshot

[@devionwatkins5638]

I want to highlight the matches in both columns how would I go about that

[@extrandrew]

Is there a way to compare lists that are on different sheets within the Spreadsheet document?

[@ProlificOaktree]

Yes, just type the name of the other sheet and an explanation point before the cell reference. So, the value of cell G3 in the sheet called sheet2 would be =sheet2!G3

[@keekiatpang3255]

Hi Thanks! Can it compare between two different sheets?

[@ProlificOaktree]

Yeah change the reference as shown here: kzbin.info/www/bejne/rHOoi5yJdplsg68

[@kesarashish]

I have 2 columns with similar content. Just want only the unique(out of the 2 columns) which don't exist in both, in a new/different column

[@ProlificOaktree]

Maybe put them both in the same column and create a pivot table to de-duplicate it.

[@bewellforlife271]

It would be helpful if you wrote the formulas in the video description.

[@ProlificOaktree]

Good idea. I just added them. Thanks.

[@romaknafel4116]

Hey! Is there a way to extract those matching words to another column or sheet? If there were thousands of words to match

[@ProlificOaktree]

I'm putting this question in the que to answer tomorrow during the live stream at 11 am. Hope to see you there. Look at my channel page for the link.

[@romaknafel4116]

@@ProlificOaktree hey! Have i missed the live stream? Could you paste here the link?

[@ProlificOaktree]

@@romaknafel4116 kzbin.info/www/bejne/d2OroHiPgJulrZY

[@abbasalyasere]

How do I in Google Spreadsheets - which maintain hyperlink formatting on import importRange + QUERY?

[@gelanikl8825]

Hi I work in a small company, we bid cars to customers after paying a guarantee from an auction site in America and after winning the car the customer pays the required value and then we ship the car Topic: I have a table with customer payments and data (date / name / car / car code / receipt / exchange / balance) And the second table in the capital movement of the car that was paid to the site and data (date / name / car / car code / receipt / exchange / balance) I cannot obtain an account statement because we are considered a branch of the main account so I used the tables My question: I want a function or method to read a column in which (the car code) is in the capital movement of the car that was purchased with the column in which (the car code) is in the customer payments column and output the difference between the first and the second table Until I know who the rest is of value and who has not paid Thank you

[@jeremymarino1726]

I'm trying to create a sheet to dump in words for online Boggle so that the words in each column would highlight if they match with words in any other column. It sounds like this is what I'm looking for but instead of two columns I'm comparing 5 columns all against each other. I can't figure out how to make that happen. Any suggestions?

[@ProlificOaktree]

Just try this with the range being all of the columns kzbin.info/www/bejne/eafchIidjrilmKM

[@shankarmallick470]

=MATCH(A2,B$2:B$258,0)

[@BigFootBox]

I like it, just trying to match it to a second sheet... Using =match(a2,2ndsheet!g$2:2ndshhet!g$30,0) is rejected ... why? and thx

[@carsonemery1846]

if i want to compare a list of 100 addresses to a master list of 1000, can I highlight the columns to do this?

[@ProlificOaktree]

Yes!

[@L3SICK]

For me works change commas for semicolon =ISNA(MATCH(D2;B$2:B$11;0))

[@ProlificOaktree]

You must be in a country that uses a comma as a thousands separator and therefore all formulas will use a semi-colon instead of a comma.

[@Grumetillo]

THANK YOU i was thinking i had a typo and could't find it (and that was driving me crazy)

[@philljasmith]

I'm trying to reference another sheet called Sheet2 - I'm trying to input this =MATCH(B2,Sheet2!C$2:C$393,0) but I'm getting an error, any idea why this wouldn't work?

[@ProlificOaktree]

No. My best advice is to try the formula in a regular cell outside of conditional formatting, get it to work there, and then paste it back in.

[@gandoedyck5938]

doesnt work anymore, or at least not for me (copied the code form your desc.) :( sad! Id like to compare 2 cells f the number is equal, greater or smaller than..

[@ericmelounou5910]

thanks it's very useful, but if like me it still doesn't work in your spreadsheet, JUST REPLACE THE COMMAS BY SEMI COLONS ! so in the sample =match(D2;B$2:B$8;0) it also work if you replace =match by =equiv(...

[@ProlificOaktree]

Thanks for the input. This is the case for anyone that lives in a country where the comma is a decimal point.

[@ganeshsalve431]

I have used this formula =COUNTIF(B:B,B2)>1 But same 2value not showing duplicate?? Please suggest here

[@jdiritto701]

Is there a way to do this across rows?!

[@ProlificOaktree]

Yes, you'll want to fix the columns instead of the rows. Something like this. I just moved the $s. =ISNA(MATCH(C1,$A1:$A7,0))

[@taranjeet8812]

Sir, can you tell me how can we find duplicate in three column.

[@ProlificOaktree]

Try combining formulas with the OR or the AND function, depending on how you want it to work.

[@JamesEdington]

What if I want to search multiple columns for matches? (not just two lists, say 3 or more lists)

[@ProlificOaktree]

Try something like this: =ISNA(MATCH(A1,B$1:C$7,0)) This would works its way down column A and look for matches in the range of B1 through C7. Expand this range as needed or make it a named range so it will automatically expand (which is a different subject).

[@ProlificOaktree]

Um, just looked at your channel. That's quite an epic tetherball tournament you had.

[@JonathanWood6]

@@ProlificOaktree Hi thanks for the video, it's really helpful and this is the specific question I have. I followed your example about searching in single columns and this works fine: =ISNA(MATCH(A2,H$2:H$10,0)) but when I change it to search across multiple columns, it no longer highlights the non-matched (since it's inverted): =ISNA(MATCH(A2,H$2:I$10,0)) -- this doesn't work thanks for your help!

[@vladmadorsky]

So this worked for me a few weeks ago...but it doesn't work now. Did Google Sheets make some updates?

[@ProlificOaktree]

No, they haven't made any update that would impact these formulas.

[@diesaffer]

me too

[@chrisr9441]

Same here... Thought I was crazy. EDIT: Just discovered the solution to my issue. I was trying to use conditional formatting to highlight cells in one column, based on matching in multiple columns. I instead created a conditional formatting rule for EACH column I was testing a match for. In other word, each column to be tested got their own rule. This worked. If your issue is the same as mine, follow the exact steps in the video. Just repeat those steps and for any additional column you're testing.

[@ZoMbIEx23x]

@@chrisr9441 I am doing the same thing but I am having an issue where it's not comparing straight across and instead comparing to the whole column.

[@premswami3262]

THANKS FOR VIDEO SIR HOW TO HIGHLIGHT MATCHING WORDS (30% OR 50% )JUST IN ONE COLUMN LIKE ( GARDEN,GARDON,NEWYORK,NEOYORK ,AMAZON,AMAJONE)