How to compare data in two Google sheets or columns

Whether there's summer knocking on our doors or winter invading Westeros, we still work in Google Sheets and have to compare different pieces of tables with one another. In this article, I'm sharing ways of matching your data and giving away tips on doing that swiftly.

Compare two columns or sheets

One of the tasks you may have is to scan two columns or sheets for matches or differences and identify them somewhere outside the tables.

Compare two columns in Google Sheets for matches and differences

I'll start with comparing two cells in Google Sheets. This way lets you scan entire columns row by row.

Example 1. Google Sheets – compare two cells

For this first example, you will need a helper column in order to enter the formula into the first row of the data to compare:


If cells match, you'll see TRUE, otherwise FALSE. To check all cells in a column, copy the formula down to other rows:
The simplest formula in Google Sheets to compare two cells.

Tip. To compare columns from different files, you need to use the IMPORTRANGE function:


Example 2. Google Sheets – compare two lists for matches and differences

  • A neater solution would be to use the IF function. You'll be able to set the exact status for identical and different cells:

    Identify pairs of cells with the IF function.

    Tip. If your data is written in different cases and you'd like to consider such words as different, here's the formula for you:


    Where EXACT considers the case and looks for the complete identicals.

  • To identify only rows with duplicate cells, use this formula:


  • To mark only rows with unique records between cells in two columns, take this one:


Example 3. Compare two columns in Google Sheets

  • There's a way to avoid copying the formula over each row. You can forge an array IF formula in the first cell of your helper column:


    This IF pairs each cell of column A with the same row in column C. If records are different, the row will be identified accordingly. What is nice about this array formula is that it automatically marks each and every row at once:
    Find differences between two columns with the array function.

  • In case you'd rather name the rows with identical cells, fill the second argument of the formula instead of the third one:


Example 4. Compare two Google Sheets for differences

Oftentimes you need to compare two columns in Google Sheets that belong inside a huge table. Or they can be entirely different sheets like reports, price lists, working shifts per month, etc. Then, I believe, you can't afford to create a helper column or it can be quite difficult to manage.

If this sounds familiar, don't worry, you can still mark the differences on another sheet.

Here are two tables with products and their prices. I want to locate all cells with different contents between these tables:
Short price lists to compare.
Start with creating a new sheet and enter the next formula into A1:

=IF(Sheet1!A1<>Sheet2!A1,Sheet1!A1&" | "&Sheet2!A1,"")

Note. You must copy the formula over the range equal to the size of the biggest table.

As a result, you will see only those cells that differ in contents. The formula will also pull records from both tables and separate them with a character you enter to the formula:
Different cells between the first two sheets have been identified.

Tip. If the sheets to compare are in different files, again, just incorporate the IMPORTRANGE function:

=IF(Sheet1!A1<>IMPORTRANGE("2nd_spreadsheet_url","Sheet1!A1"),Sheet1!A1&" | "&IMPORTRANGE("2nd_spreadsheet_url","Sheet1!A1"),"")

Tool for Google Sheets to compare two columns and sheets

Of course, each of the examples above can be used to compare two columns from one or two tables or even match sheets themselves. However, there is a tool we created for this task that will benefit you a lot.

It will compare two Google sheets and columns for duplicates or uniques in 3 steps. Make it mark the found records with a status column (that can be filtered, by the way) or colour, copy or move them to another location, or even clear cells and delete entire rows with dupes whatsoever.

I used the add-on to find the rows from Sheet1 that are absent from Sheet2 based on Fruit and MSRP columns:
Compare columns or sheets add-on.
For your better convenience, we've described all the tool's options on its help page and in this video:

Feel free to try it for yourself and notice how much time it saves you. :)

Compare data in two Google Sheets and fetch missing records

Comparing two Google Sheets for differences and repeats is half the work, but what about missing data? There are special functions for this as well, for example, VLOOKUP. Let's see what you can do.

Find missing data

Example 1

Imagine you have two lists of products (columns A and B in my case, but they can simply be on different sheets). You need to find those presented in the first list but not in the second one. This formula will do the trick:


How does the formula work:

  • VLOOKUP searches for the product from A2 in the second list. If it's there, the function returns the product name. Or else you will get an #N/A error meaning the value wasn't found in column B.
  • ISERROR checks what VLOOKUP returns and shows you TRUE if it's the value and FALSE if it's the error.

Thus, cells with FALSE is what you're looking for. Copy the formula to other cells to check each product from the first list:
Looking for products that are in column A only.

Note. If your columns are in different sheets, your formula will reference one of them:


Tip. To get by with a one-cell formula, it should be an array one. Such formula will automatically fill all cells with results:


Example 2

Another smart way would be to count all appearances of the product from A2 in column B:

=IF(COUNTIF($B:$B, $A2)=0, "Not found", "")

If there's absolutely nothing to count, the IF function will mark cells with Not found. Other cells will remain empty:
Count values to check if anything is missing.

Example 3

Where there's VLOOKUP, there's MATCH. You know that, right? ;) Here's the formula to match products rather than count:

=IF(ISERROR(MATCH($A2,$B:$B,0)),"Not found","")

Tip. Feel free to specify the exact range of the second column if it remains the same:

=IF(ISERROR(MATCH($A2,$B2:$B28,0)),"Not found","")

Pull matching data

Example 1

Your task may be a bit fancier: you may need to pull all missing information for the records common for both tables, for example, update prices. If so, you'll need to wrap MATCH in INDEX:


The formula compares fruits in column A with fruits in column D. For everything found, it pulls the prices from column E to column B.
Pull matching data using formulas in Google Sheets.

Example 2

As you may have guessed, another example would use the Google Sheets VLOOKUP function that we described some time ago.

Merge sheets using the add-on

If you're tired of formulas, you can use our Merge Sheets add-on to quickly match and merge two Google sheets. Alongside its basic purpose to pull the missing data, it can also update existing values and even add non-matching rows. You can see all changes in colour or in a status column that can be filtered.
Merge Sheets add-on.

Tip. Also, make sure to check out this video about the Merge Sheets add-on:

Conditional formatting to compare data in two Google Sheets

There's one more standard way Google offers to compare your data – by colouring matches and/or differences via conditional formatting. This method makes all records you're looking for stand out instantly. Your job here is to create a rule with a formula and apply it to a correct data range.

Highlight duplicates in two sheets or columns

Let's compare two columns in Google Sheets for matches and colour only those cells in column A that tally with cells in the same row in column C:

  1. Select the range with records to color (A2:A10 for me).
  2. Go to Format > Conditional formatting in the spreadsheet menu.
  3. Enter a simple formula to the rule:


  4. Pick the color to highlight cells.

Highlight duplicates in two columns in Google Sheets.

Tip. If your columns change in size constantly and you want the rule to consider all new entries, apply it to the entire column (A2:A, assuming the data to compare starts from A2) and modify the formula like this:


This will process entire columns and ignore empty cells.

Note. To compare data from two different sheets, you'll have to make other adjustments to the formula. You see, conditional formatting in Google Sheets doesn't support cross-sheet references. However, you can access other sheets indirectly:


In this case, please make sure to specify the range to apply the rule to – A2:A10.

Compare two Google sheets and columns for differences

To highlight records that don't match cells on the same row in another column, the drill is the same as above. You select the range and create a conditional formatting rule. However, the formula here differs:

Google Sheets – compare two lists.

Again, modify the formula to make the rule dynamic (have it consider all newly added values in these columns):


And use the indirect reference to another sheet if the column to compare with is there:


Note. Don't forget to specify the range to apply the rule to – A2:A10.

Compare two lists and highlight records in both of them

Of course, it's more likely the same records in your columns will be scattered. The value in A2 in one column will not necessarily be on the second row of another column. In fact, it may appear much later. Clearly, this requires another method of searching for the items.

Example 1. Compare two columns in Google Sheets and highlight differences (uniques)

To highlight unique values in each list, you need to create two conditional formatting rules for each column.

Color column A: =COUNTIF($C$2:$C$9,$A2)=0
Color column C: =COUNTIF($A$2:$A$10,$C2)=0

Here are the uniques I've got:
Unique ingredients for each list.

Example 2. Find and highlight duplicates in two columns in Google Sheets

You can colour common values after slight modifications in both formulas from the previous example. Just make the formula count everything greater than zero.

Color dupes between columns in A only: =COUNTIF($C$2:$C$9,$A2)>0
Color dupes between columns in C only: =COUNTIF($A$2:$A$10,$C2)>0
Highlight values that appear in both columns.

Quick way to match columns and highlight records

Conditional formatting can be tricky sometimes: you may accidentally create a few rules over the same range or apply colors manually over cells with rules. Also, you have to keep an eye on all ranges: the ones you highlight via rules and those you use in rules themselves. All of these may confuse you a lot if you're not prepared and not sure where to look for the problem.

Luckily, our Compare columns or sheets is intuitive enough to help you match two columns within one table, two different tables on one sheet, or even two separate sheets, and highlight those uniques or dupes that may sneak into your data.

Here's how I colored uniques between two tables based on Fruit and MSRP columns using the tool:
Compare columns or sheets and highlight records.

Tip. Have you seen the video tutorial for the Compare columns or sheets add-on? Check it out.

All these methods are now at your disposal – experiment with them, modify and apply to your data. If none of the suggestions help your particular task, feel free to discuss your case in comments down below.

You may also be interested in

129 responses to "How to compare data in two Google sheets or columns"

  1. Violet says:

    I have to separate Google Spreadsheets. I need to make sure that they are identical. How can I compare the data between two separate spreadsheets?

    Thank you,

    • Hi Violet,

      To reference another spreadsheet, you need to use the IMPORTRANGE function.

      Thus, your formula to compare two Google Sheets for differences may look like this:
      =IF(Sheet1!A1<>IMPORTRANGE("link_to_your_2nd_spreadsheet","Sheet1!A1"),Sheet1!A1&" | "&IMPORTRANGE("link_to_your_2nd_spreadsheet","Sheet1!A1"),"")

  2. JOY says:

    Hi everyone,
    Can somone please help me with which formula to use:
    If Value in Column B is Column B minus Column A equal or greater than 2, then highlight Column B. So for example:

    Column A Column B
    100 103 = 103 is highlighted
    100 101 = 101 is not highlighted

    Thanks to whoever can answer this.

  3. Christian V says:

    I you have a user database with all names in the SHEET1 A:A but in SHEET2!A:A you only have some of the names, and same with SHEET3A:A and SHEET4!A:A

    What if In SHEET1 you want the B:B collum to refference to what other sheet has the same name.

    So if the First name in The Sheet 1 list is «John B» And «John B» is also the 13th name in the SHEET3. How do I get SHEET!B1 to say SHEET3 when SHEET!A1 says «John B» ?

    If that makes any sence? What would the formula be in B1 (and dragged down for the rest of the sheet)

    • Hi Christian,

      If I get your task correctly, this formula will help:

      If you'd like to check in all 3 sheets at the same time and return the corresponding sheet name, try this one instead:

      Keep in mind that once the formula finds the value in Sheet2, it won't look in other sheets, it will return "Sheet2" right away. If the searched entry is absent from all three sheets, cells with formula will remain blank.

  4. Xavier says:

    Hey Guys!
    I'm having trouble doing this for two lists of names from a party. I have tried so many equations but still having trouble.
    For example, in one column I have the full list of invited guests and then in column B I have all the people who have paid. I am trying to find who hasn't paid for their ticket to the party. Can someone help me out?

  5. Patrick says:


    I have two separate Google Sheets, I need to take information from one sheet to the other. Using INDEX, MATCH how would I pull information over from one sheet to the other? MATCH("X",Sheet #2 URL, 0) doesn't seem to work


  6. David says:

    I am trying to compare/list duplicates taken from a calendar.
    Columns A and B are where people list their names and holiday dates. These columns got pretty bug quite quickly. To simplify looking for any available dates that you may want off I have created a drop down in J14 where you can select the month and which pulls the calendar for that month into cells J16:P21.
    I have taken dates from the list in column B and highlight the corresponding dates in the calendar in J16:P21.
    But, I want to list the people and the dates which they have booked off underneath the calendar.

    To recap.... compare dates from column B2:B to the calendar J16:P21 and list duplicates and the corresponding name from column A2:A

    • Hello David,

      For us to be able to help you better, please share a small sample spreadsheet with us ( with your source data and the result you expect to get. I kindly ask you to shorten the table to 10-20 rows.
      Since we keep that email for file-sharing only, once you share the file, please confirm by replying here. Thank you.

  7. Sam says:

    Hi Natalia, I am hoping you will help me with a way to identify the common row fields from the two columns. They may not be in the same row and I want to identify the common in both columns appearing in anywhere in the two columns. All the fields in the individual columns are unique. Thanks for the opportunity to ask you this question.

  8. Todd says:

    I am trying to test for conditions in two different columns and count the number of times the condition is true in both columns.
    For instance if the Value is "Closed" in column F and the Value is "M" in column K then count that row.

  9. Alexander Hudspeth says:

    Hi! This is very helpful!
    I've used =ISERROR(VLOOKUP(C3,$G:$G,1,0)), and it gives me a FALSE if the C and G columns have the same name, and TRUE if it only exists in C.
    Is there a way to list the True values in a single, tight column, excluding the False?
    My project is a list of clients that need a specific task. If it has been accomplished, then the name is in G and C. G is all clients, C is a subset of those clients with the task completed. So, I'm looking for a list of clients who need the task. Thanks in advance,


    • Hi Alexander,

      I'm sorry but your task is not entirely clear. Please consider sharing a small sample with us ( with your source data and the result you expect to get. I kindly ask you to shorten the table to 10-20 rows.
      Since we keep that email for file-sharing only, once you share the file, please confirm by replying here. Thank you.

      In the meantime, you can check our blog post devoted to Google Sheets VLOOKUP function. Perhaps, it'll help.

  10. Olumose says:

    Please, i need a formula to remove duplicates from a large data of name and phone number from two different columns. Thanks

  11. Viking says:

    Hey, you seem super knowledgable and this is about as close as I've come to a solution for my problem.

    I'm putting together a data set, which is basically a list of countries in row A, and I want to add data in form of entire columns - typically two columns where one is the list of countries and the other one is the data input. The issue is that some lists don't include all countries, for which I'd like to leave an empty row in that specific column of data.

    Here's how I want it to look:

    On the right how it ends up, compared to my full list of countries:

    I'm very greatful for any help of redirection towards a solution!

  12. Nickolas says:

    I am scraping data from the web in two columns and want to find the common values between the two columns (ie duplicates) and put them on the thrid column.
    As I am scraping the data, I do not know how many cells I will have at any given time.
    Any help appreciated!

  13. Joey says:


    I have a Google Form people filled out, in total I need about 220 responses.
    People have filled out their e-mail adresses and I want to check if everyone I sent it too actually filled it in.

    If not, I can remind them to fill it out.

    Is there a way I can crossreference the filled out email adresses with my imported list of e-mail adresses I sent the form too?
    For example, I sent the email too and
    I want to filter out which one hasnt filled out the form yet, so I can remind them.

    Not sure if you will read it, but I'll give it a shot.

  14. Hi Natalia says:

    Hi, our company needs to update the prices of its stock monthly as we get price updates from them every month. Is there a way to compare an old list of prices with the updated list and then highlight which ones have changed? At the moment we are having to do it by eye. Thank you.

  15. Gaurav Dembla says:

    Hi i wanted to know which formula shall I use when the case is: is to find if the Email id in Column B in sheet A is Equal to Email id In Column D Sheet B then Copy the Data of Column C i.e Mobile Number in Sheet A to Column C in Sheet B.

  16. Charlie Morgan says:

    Hey, thanks for posting. Rly need help here.
    I need a function for:

    If the content of Column B in sheet 1 matches the content of Column B in sheet 2, then answer YES in column E in the same row that column b sheet 1 is in.


  17. Wyatt Chong says:

    Hi, is it possible to compare 2 pivot table and calculate the sum of both pivot table?
    Pivot table 1 is tab IN, pivot table 2 is tab OUT. I need to know the sum of person A IN minus the sum of person A in tab OUT. So that I can know the actual sum of person A in certain date.

  18. Tony Francis says:

    I need to identify the different attendees year on year. Column A is all the attendees in 2017, column B is all the attendees in 2018. I want a list of persons who came in 2017 and not in 2018 and also who came in 2018 but not in 2017.
    Doing this manually is taking a great deal of time and is not reliably accurate.
    Thanks for your help

  19. Midnite Constantine says:

    I have a team of 14 people. I have to manually enter their name and email addresses in col3 and col4. Is there a way I can enter their name and their email address comes up automatically in the next col?
    Eg: Name Email address

    • Hi Midnite,

      Suppose you enter names to column A on Sheet1, and you need to have emails in column B, same Sheet1.

      To do this, first, list each and every name and its corresponding email on a separate sheet, for example, Sheet2, in columns A and B respectively.
      Then, build a VLOOKUP formula like this in column B, Sheet1:

      Once you enter the name from the list to A2, B2 will be automatically filled with the needed email.

  20. Andrea says:

    I have a google sheet of students reading levels that are letters. I would like to find the difference between for example e3-d3 to calculate growth. How do I do this? Thanks!

    • Hi Andrea,

      For me to be able to suggest you anything, please share a small sample spreadsheet with us ( with 2 sheets: your source data and the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows.
      Note. We keep that email for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying here.

  21. akan says:


    uneven match issue

  22. Nini says:

    Hi! Appreciate your help in advance! I need to compare names in Column B in Tab 2, to Column D in Tab 1 and if the name matches, pull cell data (a date) from Column E in Tab 1. Thank you!!

  23. Tommy says:

    Hi! Thank you for this tutorial! It was very helpful.

    I was wondering if it was possible to write a formula that would compare two cells for matching words regardless of order. For example, trying to compare names in two separate cells, B2 contains the text "Text Name" and cell C2 contains the text "Name Text". I would like to consider this a match and have cell D2 reflect True.

    Thank you in advance for any and all help!

  24. Cyndi says:

    I am comparing 2 large lists in separate sheets, one a master copy of all names and the other a list of names I've phoned. I'm trying to highlight the ones that are missing from list 2.
    The issue I have is that column A is last name and column B is first name on each sheet, so I'm needing to compare 2 columns to 2 columns to find discrepancies.
    How do I go about comparing, highlighting and finding who is missing from list 2?
    Thanks a million!

  25. Gwen says:

    I am trying to get a range on Sheet 1 to format based on a range on Sheet 2. Both ranges are full of checkboxes (so not empty). The data validation is identical. I know that I need to use INDIRECT to direct it towards Sheet 2, every time I get a formula that should work, it doesn't. I've gone through the entire checklist of troubleshooting and still cannot figure out what I am doing wrong. I've tried multiple comparative formulae that I've found through searching for help with this. If you can give me the correct formula to conditionally format based on another sheet, I would greatly appreciate it. Thank you in advance.

    • Hello Gwen,

      I'm sorry it's hard to suggest anything without seeing even the smallest example of your data. Please consider sharing an editable copy of a small sample spreadsheet with us ( with your source data and the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows. We'll look into and try to come up with a solution.

      Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying here.

  26. Rey says:

    I'm working on some tracking of lists of quotes from famous people. I've already used the countifs and detected the duplicates BUT the issue is that I encountered two quotes that are the same but different usage of punctuations (comma and semicolon) and of course, it will not be detected as "duplicate" because of 1 character. How can I filter this kind of scenario and mark them as duplicate? Thank you in advance!

  27. Kim says:

    Hi Natalia,
    I have a list of people (first and last names) in one Google Sheet, and a different list in another Google Sheet. Some people will appear in both sheets, but in a different order. How can I check for people with the same first and last names who appear in both Google Sheets and highlight them in both Sheets?

  28. Sonal says:

    Scenario 1 - Output sheet contains new records based on Employee ID (Which are uncommon in WHMOutputStatus & Component 1&2)

    Scenario 2 - Output sheet contains records (compare records in Component 1&2 and check whether record having “In Progress” Status. If records contains “In Progress” then dont pick that record from WHMOutputStatus. If record having Actioned status and not having In Progress - pick that record.

    for e.g WHMOutputStatus
    Emp ID Name Status
    1 ABC Actioned
    2 CDE Actioned
    3 EFG Actioned
    4 HIJ Actioned
    5 JKL In Progress
    6 LMN In Progress
    1 ABC Actioned
    2 CDE Actioned
    3 EFG In Progress
    4 HIJ In Progress

    Component 1&2
    Emp ID Name
    1 ABC
    2 CDE
    3 EFG
    4 HIJ
    5 JKL
    10 OOO
    8 YYY
    7 XXX
    Output Sheet must contains below records
    Emp ID Name Status
    1 ABC
    2 CDE
    10 OOO
    8 YYY
    7 XXX

    it would be difficult to manage both scenarios in one sheet. So i break it down into two sheet. I've achieved Scenario 1 using formula - FILTER('Component 1&2'!A:I,IF('Component 1&2'!A:A="",FALSE,(ISNUMBER(MATCH('Component 1&2'!A:A,WHMOutputStatus!A:A,0)))))

    I am looking for scenario 2 solution

    • Sonal says:

      hope this summary helps!!

      DT1 (Master DB) DT2 (RAW Data from System) Output Sheet
      Actioned - Not In progress record record available (employee ID exists) include this record from dt2
      In progress record available (employee ID exists) dont include from dt2
      Actioned - Not In progress record No record exists for same employee don’t include (not exists in DT2)
      In progress No record exists for same employee don’t include (not exists in DT2)

      • Sonal says:

        Hi @Natalia Sharashova ( Team),

        Any inputs from your end?


        • Hi Sonal,

          Our tech specialist took a look at your task. Here's a formula he came up with to compare your two tables and pull those records from 'Component 1&2' that appear in 'WHMOutputStatus' with the status 'Actioned':
          =ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(INDEX('Component 1&2'!$B$2:$B$9,SMALL(IF(COUNTIFS(WHMOutputStatus!$B$2:$B$11,'Component 1&2'!$B$2:$B$9,WHMOutputStatus!$C$2:$C$11,"Actioned",WHMOutputStatus!$A$2:$A$11,'Component 1&2'!$A$2:$A$9),ROW('Component 1&2'!$B$2:$B$9)),ROW(1:1))-1),"")), 1, 1)

          Hope this helps.

  29. Jorge says:

    You are awesome. I love this one: "Compare two columns in Google Sheets and highlight differences (unique)". But, is there a way to do the same when the values are no unique ?

    What can I do if Column A has a value twice while Column B just once?


  30. Divna says:

    Hi Natalia! Very nice of you helping people in need with your great knowledge, good work!
    I have troubles comparing multiple columns, which should be arranged by their content (witch is composed of numbers, letters and mixed), in the way that columns with similar content should be grouped together. Thanks in advance!

    • Hi Divna,

      Thank you for your lovely feedback! :)

      For me to be able to help you with your task, please consider sharing an editable copy of a sample spreadsheet with us ( Please make sure it contains 2 sheets: a sample of your source data and the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows.
      Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying here. Thank you.

  31. dracomagmus says:

    -Names- -Emails- -Rate- -Output-
    Alan $60
    Belle $65
    Cera $70
    Dan $65
    Eleanor $65
    Frank $70
    Gideon $65

    I am currently looking for help on this issue im having. Is there a way to:
    Step 1) Find all the emails in column 2 that are exact matches (all are sorted in asc orders using query function).
    Step 2) Compare the rates of those exact matches
    Step 3) Give discount to the lower rates and output the calculated amount in Column 4

    There happened to be 3 accounts using The rates are $60, $65 and $70 accordingly for those 3 accounts. 5% would be given to the $60 and $65 (as there are the lowest compared to the account with the rate $70.). Then repeat the steps for the next few aacounts under Etc etc.

    Thank you!

    • Hi Draco,

      If I've got your task right, here's a formula that works for me in the Output column:

      • dracomagmus says:

        Hi Natalia, thank you for the reply! The formula works to some extent. But I realised there is a condition I forgot to include in >.< I'm so sorry.

        'x' account with same email
        - all are same rates, give the discount to x-1 of the accounts.

        eg. 2 accounts with same email
        - if both rates are the same, give 5% disc to only 1 of them (maybe to the first account)

        eg. 3 account with same email
        - if all 3 rates are the same, give 5% disc to only 2 of them. (maybe to the first 2 accounts.)

        Will it be easier if I share the link of the Google sheet to you? =)


        • dracomagmus says:

          2nd condition
          eg. 3 account with same email
          - if 2 accounts are the same and they are higher than the 3rd account, give 5% to the lowest and to one of the highest.
          ($60, $65, $65 => $60 account and one of the $65 account will have the discount.)

          Thanks again!

          • Thank you for the clarifications, Draco,

            yes, if it's possible, please do share an editable copy of your file with us ( Please also include the tab with the expected result there.

            Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying here. Thank you.

  32. Me says:

    Wow, some of the comments you answered are amazing. It's crazy how difficult some are.
    I am hoping you can solve mine.
    Conditional formatting in Google Sheets, many columns. If the cell in the right column is greater than the cell in the left column, highlight it. Continuing that across about 50 columns each individual cell compared to the previous and highlighted only if greater than the left cell.

  33. Santo says:

    hello i have a trouble about google sheet

    i am trying to compare 2 data from 2 different sheet (same workbook)

    long story short, i want to compare SKU data from sheet "4" column H4:H and compare with sheet "Jangan Di Sentuh" colomn "N2:AH347"

    if the data from H4:H can not be found in data N2:AH347" then highlight with red color

    i have been using this formula but always "invalid formula" in conditional formatting feature

    =ISNA(MATCH(H4,'Jangan Di Sentuh'!N$2:AH$347,0))

    =ISNA(MATCH(H4;'Jangan Di Sentuh'!N$2:AH$347;0))

    any help please

    • Hello Santo,

      Here's what you need to know:

      1. MATCH can process only one-dimensional arrays: 1 column or 1 row at a time, so you need to shorten N$2:AH$347 to N$2:N$347.
      2. Direct cell references to other sheets don't work in conditional formatting. You need to use indirect references instead. We described them here.

      So here's the formula I would try:
      =ISNA(MATCH(H4,INDIRECT("'Jangan Di Sentuh'!N2:N31"),0))=TRUE

      • Susanto says:


        Every end of the months i need to check 1 by 1 each SKU, every month i have to check minimum 5000++ transactions just to make sure the SKU is correct. You save my lifeeeeee

      • Susanto says:

        What if instead different sheet, i make different workbook? so everytime there is new SKU, i didnt have to change every workbook SKU List? (i have 8 workbook from 8 different marketer)

        can i combine it with importrange like this?

        =ISNA(MATCH(H4,INDIRECT(=importrange("https://xxx","'Jangan Di Sentuh'!E2:E1427")),0))=TRUE

  34. Chris says:

    I'm trying to compare two lists of account names to find duplicates. Problem is, the two lists have some accounts that have slightly different names (for example, "Company" and "Company LLC" do not show as duplicates). I've used the highlighting option from the last Example 2 on this page, but it only highlights duplicates that are EXACT matches.

    Is there any way to highlight/identify duplicates with similar names or partial matches?

  35. Julian says:

    You guys are great helping everyone with these formulas... hope you could perhaps help me as well.

    I have a google sheets file with data entered in two columns. The data in both columns is separated by commas as such: abc,def,ghi

    So, it's possible to have "abc" for example both in column A and column B.

    I would love to be able to use a formula in column C to tell me if that specific row contains duplicates between column A and B.

    The data is scrambled so can't use any of the formulas I've been reading here.

    Any idea ? thank you in advance for the assistance :-)

  36. Eric says:

    Some please help with the formula/approach for this : How to find Cells containing at least one of the same words in another cell.
    Example CELL A contains Smith Dean K and CELL B contains Smith D Kane. In this case the result should be TRUE because both CELL A and B contains 'Smith'

  37. John says:

    I have two lists of names. On one sheet the name is in one column (cell) and in the other sheet it is split into 2 or 3 columns. I need to highlight the names from the single column (Doe, John A) to the sheet that has them as (Doe) (John) (A).

    • Hello John,

      If you want to skip the steps where you merge name parts in a helper column, you will have to do that directly in your conditional formatting rule formula. It may look like this:
      =MATCH(CONCAT(JOIN(", ", $C2:$D2),IF(COUNTA($E2)>0," "&JOIN(" ",$E2),"")),INDIRECT("Sheet4!$A$2:$A$30"))>0

      Where C2:D2 and E2 are name parts (the one that is split) and INDIRECT references a single column with names in another sheet.

  38. Anusha Bindal says:

    Hi. I want to compare columns and find duplicate values between two sheets, can you please help me with it?

  39. Robert says:

    I have 4 different people using 1 google sheet each. In column B of each sheet is the unique id (expressed as a number if this matters) of each client. I want to be able to see where there are duplicates of any client id in any of the 4 sheets.
    By this I mean, if sheet 2 has client Z and sheet 3 has client Z, I would like this highlighted. I know how to use conditional formatting to do this on one google sheet.
    However, to do this across the 4 sheets, I thought that I would create a 5th google sheet and import all the data in column B onto sheet 5 then have the same conditional formatting.
    My issue now...
    I am using this formula
    =IMPORTRANGE("", "B2:B700")
    and this works for the data on sheet 1
    When I try to do the same with sheet 2, I am having issues. I have tried the below formula but it is having issues. Error codes.
    =IMPORTRANGE("", "sheet2!B2:B5")
    The above is even an error code when I try to take the url for the second sheet and plug that in.

    Any ideas? Much appreciated!!

    • Hello Robert,

      Please look through this explanation on how to use the IMPORTRANGE correctly.

      If you're still getting errors even after following those steps, please specify what errors you're getting exactly. Also, if possible, create a shortened version of your spreadsheet and share it with us ( for checking. NOTE. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying here.

  40. Ashwini Amber says:

    Hi Nat

    can you please help me with below scenario

    I have to check for 3 column ( like ID, date, Amount) from file 1 and if these 3 column matches with file 2 then return Transaction_no to 4th Colum from file 2 to file 1 else no match return "not found/ pending"

  41. shujaat says:

    I wanted to match 1 col with 1 item and place value from other Col into specified col.
    So I was able to do that with =SUMIF(A1:A100,C1,B1:B100)

    But now when I change col A to Dropdown list items and using this formula its giving completely wrong values.
    Anyone know any specific formula for doing this with dropdown values?

    • Hello Shujaat,

      A drop-down is a single cell with only one record available at a time. The formula can only look at whatever record is currently selected from the drop-down. Thus, if you want to test all 100 cells in column A against the SUMIF criteria, I'm afraid the drop-down is not an option.

  42. Ash says:

    If I have have a list of 8 digit numbers in lets say column A1:A50 and i have a similar (but slightly different) list in column B1:B50, how can i check if the list of numbers in A1:A50 match/occur with B1:B50?

    Als i am using google drive Excel for this in Dutch

    • Hello Ash,

      The ways described in this blog post work with complete matches only. As for your records, could you please give me an example of numbers from column A and their similar values from column B?

      • Ash says:

        Thank you for your reply, here is an example:
        A B
        12178715 12178841
        12178784 12179559
        12182831 12181891
        12182835 12182921
        12182843 12183293
        12182908 12183535
        12183449 12183859
        12183486 12183899
        12183508 12184062
        12183871 12184377
        12184246 12184884
        12184345 12184908
        12184681 12184955
        12185347 12184959
        12185363 12185770
        12185454 12186686
        12185471 12186958
        12185627 12188055
        12185671 12188133
        12185719 12188145
        12185760 12188219

  43. Quinten De Wilde says:


    I have thousands of data in 2 columns

    First one is ordered by export and the other by latest activity. The export columns has more queries than the latest activity. (can't change the export to the latest activity)

    I stuck in finding a way to get all the email addresses from the latest activity and check where they are in the export list.
    ***And important sidenote: the latest activity list is incomplete at the end of each query. As the example below.*** name3@mail.c name4@mail.coun name6 name1@ma name2@ma

    I need to **find** and **keep** all the duplicates and **remove** all the others

    Is this something I can do in Excel automated instead of manually finding each one by `cmd+f`.

    I have about 5000 active users I need to filter out.

  44. Quinten De Wilde says:

    The Excel logic in the link also doesn't solve the issue.
    Because it is all based on the fact that the matching queries are on the same row.
    I'm searching for matching queries in all the rows of a column.

    I'm stuck on how to fix this problem...

    • Hi Quinten,

      Thank you for the confirmation. If you're going to use formulas, you can't build them in column A while processing column A. You'll have to use a helper column and put formulas there.

      1. First, pull data from column A based on partial matches in column B using INDEX/MATCH:
        =INDEX(A2:A7,MATCH(B2&"*", B2:B7, 0))
      2. Now the new column features only those names from column A that also appear in column B. But these names may duplicate themselves (if INDEX/MATCH found matches a few times). To remove duplicates, first convert formulas to values. Then go to Data > Remove Duplicates to remove duplicated names.

      Hope this helps!

  45. Quinten De Wilde says:

    Thanks so much for the quick answers.

    I've put the following code in column "c"

    =INDEX(E2:E17400 , MATCH(M2&"*", M2:M4901 , 0))

    I get this error

    Parser error in formula.

    The ranges are correct though..

  46. Manisha says:

    I want to compare 2 google sheets is old sheet and the other is new sheet and having huge amount of data . I want to highlight those values/rows that are modified and deleted in the new sheet by comparing with old sheet.
    What formula can I use in conditional formatting to highlight it in the new sheet itself?

  47. Maricel says:

    How can I check duplicate across all sheets? Let say I got 5 different sheets. Thank you

  48. Windy says:

    Hi Natalia,

    How about if you compare values on different columns?

  49. Isaac G says:

    Hello there! This is a great reference, but I'm having issues comparing lists of differing lengths. I have one list that is 400 records, the other list is 2600 records. For whatever reason, I get false positives (or negatives depending on how I run the formula). Is there a way to construct the formula to account for such vastly different list lengths that won't return false pos/negs?

  50. Atousa says:

    hi there
    i want to know the exact formula to find similar data in "one" column
    mean find similar cells in just one column not 2 or more
    just one
    we have any formula for this?

  51. Kate says:

    Thank you for this!! I needed to use conditional formatting to match two columns, could not figure it out for the life of me, and your explanation has made it so simple :)

  52. Christophe says:


    I'd like a formula to intersect 2 columns.
    For instance, if I have A B C D E in column A and K E T A M R G in Column B, I'd like A E in column C

    Thanks for your answer

    • Hi Christophe,

      I'm sorry but your task is not clear. For me to be able to help you, please share a small sample spreadsheet with us ( with 2 sheets: (1) a copy of your source data (2) the result you expect to get (the result sheet is of great importance and often gives us a better understanding than any text description). I kindly ask you to shorten the tables to 10-20 rows.

      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

Post a comment

Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)