Compare Two or More Lists in Excel with IF and COUNTIF

As I previously discussed, Excel provides many useful ways to automatically compare two lists of data or information. In our other example we compared two lists of four digit account values; for this example we’ll compare two lists of names.

You and a friend are throwing an epic party and have each maintained separate guest lists to track who’s coming. After a few weeks you decide to compare both lists to make sure everyone on your friend’s list (List B) is also on your list (List A).

How to do it

  1. Arrange the lists in two columns with List A in column A and List B in column B.
  2. Create a third column in column D called List C (leave column C blank for easier readability).
  3. In cell D5 enter the formula: =IF(COUNTIF(B:B,A5),A5,0) and press Enter.

    Enter formula into cell D5
    Enter formula into cell D5.
  4. Select cell D5 and navigate to Home → Conditional Formatting → Highlight Cell Rules → Equal To…
  5. In the Equal To dialog box, type 0 and click OK.
  6. Select cell D5 down to the end of the lists and press Ctrl+D to copy the formula and conditional formatting down.

    Copy the formula down to the end of the longer list (List B in this example).
    Copy the formula down to the end of the lists.

The Result

A name in List C means the corresponding name from List A was also found within List B. In the screenshot above, cell D5 displays the “Moon Barrientos,” which means that name was found on both List A and B. On the other hand, cell D12 displays Red Zero which means the corresponding name from List A (in this case, “Aracely Rock”) does not exist within List B.

This example demonstrates how to determine if a particular value – in this case a name – in List A also exists within List B. To find the opposite – whether a name in List B also exists within List A – simply change the formula in cell D5 to: =IF(COUNTIF(A:A,B5),B5,0) and press Enter. Then copy that formula down to the end of the lists.

How & Why it Works

Our formula makes use of two functions, IF and COUNTIF. The IF function checks whether a condition is met, and returns one value if TRUE, and another value if FALSE. COUNTIF counts the number of cells within a range that meet the given condition.

The Formula


COUNTIF has two required arguments, or inputs, to work:

  • range (where should Excel look, B:B in our example)
  • criteria (what should Excel find, A5 in our example)

COUNTIF checks column B (using B:B checks the entire column) for the name in cell A5. If it finds the name (i.e. the criteria you specified was met) it returns 1, otherwise it returns 0. Note: when COUNTIF returns 1 or 0, Excel treats that as TRUE (1) or FALSE (0).

The IF function has three required arguments, or inputs, to work:

  • logical_test (any value or expression that can be evaluated to TRUE or FALSE)
  • [value_if_true] (what value should Excel return if if logical_test is TRUE)
  • [value_if_false] (what value should Excel return if logical_test is FALSE)

In our example COUNTIF is the logical_test that gives the IF function either a 1 or 0, depending on whether it finds the name we specified from cell A5 within column B. If it finds the name (i.e. the logical_test is TRUE), we specified A5 as the [value_if_true] so Excel will display that name and move on. If it does not find the name (i.e. the logical_test is FALSE), we specified 0 as the [value_if_false], so Excel will display 0 and move on.

You can quickly see a list of names missing from List B by filtering List C to show only “0.” The conditional formatting just makes it easier to spot the missing values.

Practice Yourself

The best way to learn is to practice yourself, so click the link below to download the Excel 2010 workbook used to show the methods described in this post. Please feel free to share!

Compare Two or More Lists with IF and COUNTIF

  19 comments for “Compare Two or More Lists in Excel with IF and COUNTIF

  1. Max
    January 28, 2014 at 8:11 AM

    Worked like a charm, thank you!
    I used the formula as above but changed the true value, second last in formula to 1 in stead of a cell number. Counted all the ones and zeroes in the third column and it gave me the number of matches in both columns. Great!

    • March 6, 2014 at 2:12 PM

      Glad I could help, and nice work modifying the formula to better fit your needs!

      • Preethi
        March 11, 2014 at 3:03 AM


        I have three lists and I need to find out which values are common to all three.

        For example, I have three lists – A, B, C, each containing names of various cities around the world. I need to list out those (common) cities that present in all the three lists.

        Would you tell me formula would be best suited for this scenario.

        Thanks much.

        • March 28, 2014 at 5:20 AM

          I cant give you a formula, but i would start by merging the three lists into one, like described here: for example. Then i would use a “count column”, that will show a number absed on how often data is present. Then you just sort by number, and every row with the value “3” is what you want. There is probably a better way to do it, so maybe you want to wait for that. I hope i could help you, anyway.

        • Crimson_Rhallic
          September 9, 2015 at 1:17 PM

          =if( And (Countif(B:B,A1), Countif(C:C,A1)), TRUE, FALSE)

          This will check to see if A1 appears in both list 2 and list 3. If it only appears in list 1 (and maybe 1 other), it will trigger FALSE. We do not need to check the other lists, since if it does not appear in list 1 (A:A), then it cannot appear in all 3 (only 2 at best).

      • Deb
        December 10, 2014 at 12:47 PM

        Thank you!! Worked great.

  2. Nicola
    July 9, 2014 at 4:48 PM

    Can you adapt the formula so that is shows what isn’t there? So the above formula will pull through the matching and then show a 0 but I would like it to show the cell content that doesn’t appear in the list. Any ideas?!

    • Matt
      October 24, 2014 at 2:03 PM

      Flip the A5 with the zero. Think of it as inverting your question in the =IF statement. What you want instead is COUNTIF(“range of names”, “Cell#ofName you want to see”),””,”Cell#ofName you want to see”). The “” functions as a blank instead of a zero.

      I’ve also used =IF(ISERROR(MATCH(“name of person”,”Range of array”,0)),”name of person,””)

      What I want/am working on now is either the follow up formula, or a way to nest this into one, that outputs the actual names in a new list in column D. Filtering works great, but it’d be nice to just get the values of everything so you can select it without catching all the garbage in between.

  3. Rico
    August 18, 2014 at 9:10 AM

    What’s the password for the example? It’s sheet protected.

  4. September 19, 2014 at 3:28 AM

    Even though i have used your formula it displays only the formula not the result.
    Can you say What wrong i did?

    • October 25, 2014 at 11:34 AM

      Select the cell in question, then Press CTRL + ` (grave accent). It’s the key often located just below the “esc” key, and just to the left of the “1” key.

  5. ashish mehra
    September 26, 2014 at 3:56 AM

    If you want to know more about “Using If Function to Compare Dates of Two Cells”, check this link ……..

  6. Obniz
    October 3, 2014 at 9:50 AM

    Compute the grades based on following criteria (show the logic)
    If percentage >= 80 and < 90 then grade = B

  7. Obniz
    October 3, 2014 at 9:51 AM

    in Excel

  8. Anne
    March 24, 2015 at 12:01 PM

    Brilliant Explanation, thanks very much

  9. C
    June 29, 2015 at 4:15 AM

    awesome! thanks for sharing 🙂

  10. S
    September 22, 2015 at 3:45 PM

    I have in sig fig format. The sig figs match, but always return as a non-match because the values are rounded. Is there a way to return as a match?

  11. Sydney Kumbani
    February 13, 2016 at 10:10 AM

    Ooooo!!!!! what a wonderful formular buuuuuuu!!!!!!! it worked on my data, thank you very much God bless you abundantly…

Say Something