Compare lists / Excel guide

Compare two columns in Excel

Use a presence check to find missing values. If duplicates matter, count each value on both sides before deciding that the lists match.

You can do both inside Excel. The formulas below compare exact text, so IDs such as 00123 and 123 stay separate when the source cells contain those strings. They do not change your data, sort your lists, or require an add-in.

Set up a small example first

Put the first list in A2:A7 and the second in D2:D5, with headers in row 1. Format those two ranges as Text before entering the IDs. A2:A7 contains 00123 four times, followed by 00456 and 123. D2:D5 contains 00123 twice, followed by 00456 and 00789.

The order differs, and that is fine. This is a membership comparison, not a row-by-row comparison such as =A2=D2. Checking only corresponding row positions would report differences caused by sorting rather than missing records.

Find values in A that are missing from D

Enter this formula in B2 and fill it down to B7:

=IF(A2="","",IF(SUMPRODUCT(--EXACT($D$2:$D$5,A2))>0,"In both","Only A"))

For each non-empty A cell, EXACT checks every cell in D2:D5. The double minus converts TRUE and FALSE to 1 and 0. SUMPRODUCT adds those values, giving the number of exact matches. A count above zero produces In both; zero produces Only A. The first IF keeps empty input cells blank.

In the example, the first five A values appear somewhere in D. The final value, 123, returns Only A. It does not match 00123. The dollar signs keep the lookup range fixed while A2 becomes A3, A4, and so on as you fill down.

EXACT is case sensitive and does not ignore extra spaces. That is useful for identifiers, but it means abc and ABC differ. Microsoft's EXACT documentation describes the text comparison; the SUMPRODUCT documentation explains how the array results are combined.

Check the other direction

A check from A to D cannot find a value that exists only in D. Put this formula in E2 and fill down to E5:

=IF(D2="","",IF(SUMPRODUCT(--EXACT($A$2:$A$7,D2))>0,"In both","Only B"))

The value 00789 returns Only B, meaning it exists in the second list but not the first. This reverse check matters even when the two lists have the same number of rows. A missing item and an extra item can cancel out in a total row count.

Compare the duplicate counts

The presence check labels 00123 In both. That does not tell you that one list has four occurrences and the other only two. To see the counts separately, place the following formula in an unused column beside A2:

=IF(A2="","",SUMPRODUCT(--EXACT($A$2:$A$7,A2)))

In the next unused column, count the same value in D:

=IF(A2="","",SUMPRODUCT(--EXACT($D$2:$D$5,A2)))

For A2, the results are 4 and 2. If you only need the difference, use this combined formula instead:

=IF(A2="","",SUMPRODUCT(--EXACT($A$2:$A$7,A2))-SUMPRODUCT(--EXACT($D$2:$D$5,A2)))

The result is A's count minus D's count. Positive means more occurrences in A; negative means more in D. Zero means equal counts for that value. Fill down to evaluate the remaining A values. Repeated values will repeat their count result, which is expected. Use a separate distinct-value list if you want one summary row per ID; do not delete duplicates from the original data before counting.

You still need the reverse presence check to catch values found only in D. A count formula filled down beside A has no row on which to report 00789. In a larger reconciliation, build the summary from the union of both lists, then count each summary value on both sides.

When a shorter COUNTIF formula is enough

For ordinary names where case should not matter and values do not contain wildcard characters, COUNTIF is more concise:

=IF(A2="","",IF(COUNTIF($D$2:$D$5,A2)>0,"In both","Only A"))

COUNTIF is case insensitive and treats * and ? as wildcards in criteria. It also has special handling for criteria that look like numbers or comparison expressions. For literal IDs or values containing these characters, use the EXACT formulas above. Microsoft's COUNTIF reference covers the criteria rules.

Keep the original IDs intact

Setting a cell to Text after Excel has already changed 00123 to 123 cannot recover the missing zeros. Import the file with the ID column typed as Text before loading it. Depending on your Excel version, automatic data conversion settings may also prevent the conversion. Microsoft documents these options in Keeping leading zeros and large numbers.

A custom number format can display extra zeros while the underlying value remains numeric. Decide whether you need that display or an actual text identifier. This distinction also matters for long IDs: Excel's numeric precision limit can change digits that a text comparison would otherwise preserve. Start again from the original export if information has already been lost.

Apply the formulas to your own ranges

Replace A2:A7 and D2:D5 with your actual data ranges, excluding the headers. The lists may have different lengths. Keep lookup ranges absolute with dollar signs. Leave enough space for the result columns and preserve a copy of the source data while you check your setup.

Use bounded ranges with SUMPRODUCT rather than whole-column references. Repeating an array comparison on many rows can make a large workbook slow. For a regular workflow, Power Query's grouping and merging features may be a better fit. Confirm its column types before matching and count the grouped rows if duplicates are meaningful.

The examples use English Excel function names and commas between arguments. Regional settings may require semicolons, and localized Excel versions may use translated function names. SUMPRODUCT handles these comparisons without a Ctrl+Shift+Enter array entry. Check the six-row example before applying a formula to a large sheet.

If you prefer a separate report, open a similar count example in List Reconcile. That sample also repeats 00456 twice on each side. The browser tool shows the same presence and count distinction, lets you inspect the original rows, and exports an Excel workbook with IDs stored as text.