Compare Lists in Excel: Find Matches and Differences

How-to basics · Updated November 19, 2026

With values in A2:A6 and B2:B5, enter =IF(COUNTIF($B$2:$B$5,A2)>0,"In both","Only in A") in C2 and fill it down to mark each item in List A as a match or an item missing from List B. To find items present only in List B, reverse the two ranges; the right method depends on whether you need to compare membership, duplicates, or items in the same row.

What comparing two lists means

People use “compare lists” to describe several different checks. You might want to know whether a product code in one column appears anywhere in another, identify values missing from either list, compare counts when a value appears more than once, or check whether two cells on the same row are equal. These are related tasks, but they do not produce the same answer.

The membership check in the opening example asks whether each A value occurs at least once in B. It does not require the lists to be in the same order. If both contain Apple, Pear, and other values in different positions, it still finds those shared values. It also does not prove that the lists are identical: an extra item or a different number of duplicate entries can remain unnoticed unless you check for those separately.

For example, imagine A contains Apple, Pear, Plum, Pear, and Kiwi, while B contains Pear, Apple, Grape, and Pear. Apple and Pear occur in both lists; Plum and Kiwi are only in A; Grape is only in B. Pear appears twice in each list, but a basic membership test only reports that it exists in both.

Prepare the data before comparing

Put each list in its own column, with one value per cell and a clear header in row 1. Keep headers out of the formula ranges, and check that the ranges cover all populated rows. If you add data later, update the ranges or convert the data to an Excel table so formulas can refer to expanding columns.

Consistency matters. A cell containing “North ” with a trailing space can behave differently from one containing “North”; values that look like numbers may also be stored as text in one list and as numbers in the other. Check for extra spaces, inconsistent punctuation, different date formats, and leading zeros in identifiers such as postal codes. For codes, use text consistently when the zeros are meaningful.

  • Remove blank rows inside a list if they are accidental, and decide whether empty cells should be ignored.
  • Do not sort one column independently if its rows are meant to stay paired with another column.
  • Keep a copy of the original data before applying cleanup or transformations.

Mark matches and missing values with COUNTIF

In the example, A2:A6 contains Apple, Pear, Plum, Pear, and Kiwi, and B2:B5 contains Pear, Apple, Grape, and Pear. With those ranges, enter =IF(COUNTIF($B$2:$B$5,A2)>0,"In both","Only in A") in C2, then copy it through C6. The results are In both, In both, Only in A, In both, and Only in A, respectively.

COUNTIF counts how many cells in the specified range meet a condition. Here, it checks how often the current A value appears in B. If the count is greater than zero, IF returns In both; otherwise, it returns Only in A. The dollar signs lock the B range as the formula is copied down, while A2 changes to A3, A4, and so on. This is why filling the formula down is important.

To check B against A, with A2:A6 and B2:B5 as above, enter =IF(COUNTIF($A$2:$A$6,B2)>0,"In both","Only in B") in C2 beside the first B value and fill down to C5. This identifies Grape as Only in B. You can put this check in another column or on a separate sheet to keep the two directions clear.

If blank cells should stay blank instead of being labeled as matches, with the same A and B ranges, use =IF(A2="","",IF(COUNTIF($B$2:$B$5,A2)>0,"In both","Only in A")) in C2 and fill down. This prevents an empty A cell from receiving a result. Adjust the ranges to fit your actual data.

Highlight differences with conditional formatting

Color can make missing entries easier to scan, especially when you want to keep the original list visible. To highlight values in A that do not appear in B, select A2:A6, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With A2:A6 and B2:B5 as the comparison ranges and A2 as the active top cell, enter =COUNTIF($B$2:$B$5,A2)=0 as the rule formula. Choose a format, such as a fill color, and confirm.

The rule checks each selected cell against the B range and highlights it when the count is zero. The B range is fixed, but the A2 reference is relative so the rule adjusts for each row. To highlight items in B that are missing from A, select B2:B5 and use =COUNTIF($A$2:$A$6,B2)=0 instead. Conditional formatting changes the display, not the underlying values, so you can still filter or copy the data normally.

Return a separate list of differences

If you use Microsoft 365 or Excel 2021 or later, FILTER can spill matching results into nearby cells without copying a formula down. With List A in A2:A6 and List B in B2:B5, enter =FILTER(A2:A6,COUNTIF(B2:B5,A2:A6)=0,"No values found") in an empty cell. It returns Plum and Kiwi, in their original order, because those are the A values absent from B. Leave enough empty cells below the formula for the results to spill.

This formula is useful for creating a working list of exceptions, but it returns each qualifying row from A. If Plum appears twice in A and not at all in B, it will appear twice in the result. To get the differences from the other direction, swap the lists in the formula. FILTER is not available in older perpetual versions such as Excel 2019, so use the COUNTIF helper-column method there.

Duplicates, capitalization, and exact matching

COUNTIF is suitable when the question is “Does this value appear at least once?” It does not compare duplicate quantities. If A contains Pear twice and B contains it once, both A rows still say In both. When duplicate counts matter, first count occurrences in each list and compare those counts, or use a PivotTable to summarize each list by value and compare the resulting totals. A PivotTable is especially helpful with large lists or when you need a readable count report.

COUNTIF text matching is not case-sensitive, so Apple and APPLE are treated as the same text for this check. It also supports wildcard characters: an asterisk can represent any sequence of characters and a question mark can represent one character. If your actual values contain those symbols and you need literal matching, account for that rather than assuming every COUNTIF criterion is interpreted as plain text.

For a case-sensitive check, with the value to test in A2 and the comparison range in B2:B5, enter =SUMPRODUCT(--EXACT(A2,B2:B5)) in C2. It returns the number of case-sensitive matches, so a result greater than zero means an exact capitalization match exists. EXACT also distinguishes uppercase and lowercase letters, unlike COUNTIF. This is a more specialized choice; ordinary names and product lists usually do not need case-sensitive matching.

Leading or trailing spaces and invisible characters are another common source of apparent mismatches. Inspect suspicious cells in the formula bar and clean the source data consistently before comparing. Excel’s TRIM function removes ordinary extra spaces at the start and end, but imported nonbreaking spaces may need separate cleanup. Avoid changing identifiers automatically unless you know which characters are significant.

Compare corresponding rows instead

If each row in A is supposed to match the value on the same row in B, use a row-by-row comparison rather than COUNTIF. With paired values in A2:A20 and B2:B20, enter =IF(A2=B2,"Same","Different") in C2 and fill down. This compares A2 with B2, A3 with B3, and so forth. In contrast, the COUNTIF method would report a match even if the value appeared elsewhere in B.

Row-by-row comparison is useful for checking two versions of an ordered report, a before-and-after export, or paired records. It depends on the rows being aligned correctly. If one list has been sorted or has an inserted row, later comparisons may show differences even when the underlying records are present in both lists. In that case, compare by a unique ID using a lookup or membership check instead of assuming row positions correspond.

Common problems and how to resolve them

If results look wrong, check the formula references before changing the data. A missing dollar sign can cause the comparison range to shift as you fill down; a range that stops too early can omit newly added values. Also confirm that the formula starts on the same row as the first value being tested and that you filled it through the entire list.

  • Everything says Only in A: Look for text-versus-number differences, spaces, punctuation, or an incorrect B range.
  • Some blanks say In both: Use the blank-handling version of the formula or exclude empty rows.
  • Duplicates seem to be ignored: The membership test checks for at least one occurrence, not equal duplicate counts.
  • Results change after sorting: Membership formulas are order-independent; row-by-row formulas are not. Keep paired columns together when sorting.
  • A value with a symbol behaves unexpectedly: COUNTIF treats asterisk and question mark as wildcards. Test the data or choose a method suited to literal matching.

When investigating, try a small sample with a known answer and inspect the exact cell contents. Sorting each list can help people scan them, but sorting alone does not identify one-sided values and can damage row relationships if only one column is sorted. A helper column is usually the easiest way to make the check repeatable and auditable.

Frequently Asked Questions

How do I compare two columns in Excel and find what is missing?
Use COUNTIF in a helper column to test each value in the first list against the second, returning a label when the count is zero. Repeat with the ranges reversed to find values missing from the first list.

Can I compare lists in Excel without sorting them?
Yes. COUNTIF checks whether a value appears anywhere in the other range, regardless of order. Sorting is optional and should not be used as a substitute for checking membership.

Does Excel’s list comparison count duplicates?
A basic COUNTIF membership test detects whether a value occurs at least once, but it does not establish that duplicate counts are equal. Compare occurrence totals or summarize the lists with a PivotTable when frequency matters.

How can I compare two lists in Excel and highlight differences?
Select the cells in one list and create a conditional-formatting rule with a COUNTIF formula that returns zero for values absent from the other list. Repeat for the second list if you need to highlight differences in both directions.

Why does Excel say two values do not match when they look identical?
One value may contain extra spaces or hidden characters, or the two values may have different data types or formatting. Check the source cells, standardize the data, and then run the comparison again.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.