Google Sheets - Compare Two Lists for Matches or Differences 

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

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

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

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

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

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

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

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.

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!

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

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. :)

Thanks for this- clear, concise, and it works! Much appreciated.

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

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

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

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

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

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

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

This was super helpful and concise. Thank you!

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)"

Thank you ! Exactly what i was searching for :)

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

Just what I was looking for, thanks !

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

This was so helpful! Thank you for the tutorial.

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

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

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

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.

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

This is super helpful!! THANK YOU SO MUCH!

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

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.

This is very helpful dude, thanks a ton

Thank you so much!

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

Very useful! thank you for sharing!

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

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?

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

a thousand times thank you!!!!

works! you're a genius thank you so much

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

Hi. My goal is to find a way to copy a cell and text color to copy the origination of a cell's and text's colors. For example, cell a2 has the word "completed" and the text is bold red with the cell's color purple. When I type "completed" in cell c35, it automatically makes that cell's color purple, and the word becomes bold and red. How can I do that without a script?

this was so cool. thank you!

Thanks for information and working method

Super helpful. Thank you!

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

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?

Thank you, that helped me a lot.

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

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

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.

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

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

Thanks for such informative video

So helpful thank you!

SAVED MY LIFE

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

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

Thanks! No more manual comparing.

Great explanation, thank you

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

legend! works perfectly

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

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.

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,

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?

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

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.

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

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?

Hi Thanks! Can it compare between two different sheets?

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

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?

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?

strange. I have an error. I even tried to do the same sheet, as you do on the video and copypaste the formula.

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?

How about highlighting all duplicates in both columns?

Thank you! It works with semicolons for me. Russia

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?

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

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

Thanks, EXCELLENT VIDEO!

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

Thanks for your video, but formula is not visible clearly, please highlight full formula in subtitle

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

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

You rock! worked great!

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

I did the same thing and it tells me the formula is invalid ....

instead of B$2:A$8 can i use a named range ?? because comparison list is from another sheet

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

THANK YOU SIR BUT IF THE COMPARE 3 LIST, HOW IS THE FORMULA SIR?

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

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

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

Can't seem to get it to work! Range: H3:H220 Custom Formula Is: =MATCH(H3,B$3:B$217,0) The entire range just stays highlighted green

Is there a way to do this across rows?!