Excel Compare Two Columns on Two Sheets: Formula Guide

Formulas & functions · Updated November 21, 2026

To compare values in the same row on two worksheets, enter =IF(A2='Sheet 2'!A2,"Match","Different") in a helper column on the first sheet and fill it down. To check whether each value appears anywhere in the other sheet’s column, use =IF(COUNTIF('Sheet 2'!$A:$A,A2)>0,"Found","Missing"); choose this method when row order may differ.

Choose the comparison that fits your data

“Compare two columns on two sheets” can mean two different tasks. A row-by-row comparison asks whether the value in one row matches the value in the corresponding row on the other worksheet. A membership check asks whether a value appears anywhere in the other column, regardless of its row. These methods can produce different answers, so decide which question you need answered before copying a formula.

For example, suppose Sheet1 has product codes in A2:A100 and Sheet 2 has codes in A2:A100. If rows represent the same products in the same order, compare A2 with A2, A3 with A3, and so on. If one list may be sorted differently, checking corresponding rows would flag ordinary ordering differences; use a lookup-style membership check instead.

  • Same records, same row order: compare cells on the same row.
  • Two lists that may be in different orders: check whether each value occurs in the other list.
  • Need to identify additions and removals: run a membership check in both directions.

Compare corresponding rows with an IF formula

For a direct comparison, put the result in an empty column on Sheet1. Assuming the values to compare are in Sheet1!A2:A100 and Sheet 2!A2:A100, enter this in Sheet1!B2:

=IF(A2='Sheet 2'!A2,"Match","Different")

In this example, the formula returns “Match” if the two cells are equal and “Different” if they are not. Copy or fill the formula down through B100. In Microsoft 365 for Windows, select B2 and drag its fill handle down, or select the destination range and press Ctrl+D after entering the formula in its top cell. On Mac, use Command+D to fill down. Excel for the web also supports the fill handle and Ctrl+D in many keyboard layouts.

The reference to A2 on Sheet1 changes to A3, A4, and so forth as the formula is filled down. The reference to the other worksheet changes in the same way. If the worksheet name contains a space, put it in single quotation marks, as in ‘Sheet 2’!A2. A sheet name without spaces can be written without quotation marks, such as Sheet2!A2.

This test compares the cell values, not their formatting. It does not report whether one cell is bold, has a different fill color, or uses a different number format. Also, Excel’s ordinary equality comparison is not case-sensitive: “ab12” and “AB12” count as equal. A genuinely empty cell compared with another empty cell returns “Match,” while an empty cell compared with a populated one returns “Different.”

Find values anywhere in the other column

When the two lists are not in matching row order, use COUNTIF to search the comparison column for each source value. Assuming the list to check is in Sheet1!A2:A100 and the reference list is in ‘Sheet 2’!A2:A1000, enter this in Sheet1!B2:

=IF(COUNTIF('Sheet 2'!$A$2:$A$1000,A2)>0,"Found","Missing")

For A2, the result is “Found” if at least one equal value occurs in the specified range on Sheet 2, and “Missing” if none does. Fill the formula down to check the remaining values. The dollar signs keep the lookup range fixed as the formula moves down; A2 stays relative so each row checks its own value. Use a range large enough to include all reference entries. A whole-column range such as $A:$A is also valid, though a bounded range can make the intended data area clearer.

COUNTIF counts matching cells, so you can also display the number of occurrences. Assuming the same source and reference ranges, enter =COUNTIF('Sheet 2'!$A$2:$A$1000,A2) in Sheet1!B2. A result of 0 means no match; 1 means one match; a larger number indicates duplicates in the reference list. This is useful when duplicates matter, because a simple “Found” result does not distinguish one occurrence from several.

To identify items that appear in Sheet 2 but not Sheet1, put a corresponding membership formula beside the values on Sheet 2 and search Sheet1’s list. Checking only one direction finds values missing from the reference list, but it does not reveal extra values present only in the reference list.

Show both missing and extra values

A two-way comparison is often the clearest way to reconcile lists. First, next to each value on Sheet1, check whether it exists in Sheet 2. Then, beside each value on Sheet 2, check whether it exists in Sheet1. The first pass identifies entries unique to Sheet1; the reverse pass identifies entries unique to Sheet 2. This works even when the lists have different lengths or orders.

For example, assume Sheet1 has codes in A2:A100 and Sheet 2 has codes in A2:A120. In Sheet1!B2, use =IF(COUNTIF('Sheet 2'!$A$2:$A$120,A2)>0,"In both","Only on Sheet1") and fill down. In ‘Sheet 2’!B2, use =IF(COUNTIF(Sheet1!$A$2:$A$100,A2)>0,"In both","Only on Sheet 2") and fill down. The first formula labels values as “Only on Sheet1” when no match exists in the other list; the second provides the reverse result.

These formulas compare whether a value exists, not how many times the lists contain it. If Sheet1 contains the same code three times and Sheet 2 contains it once, each occurrence on Sheet1 will still be labeled “In both.” To reconcile quantities or duplicate counts, compare the COUNTIF results for each value or summarize the lists before deciding whether they balance.

Highlight matches or differences visually

A helper formula is useful when you want an explicit result, but you can also color those results. The simplest reliable route is to add a helper column first, then apply formatting to that column or the original values. On Windows, select the result cells and choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains to format text such as “Different” or “Missing.” Choose a formatting style and confirm. On Mac, the Conditional Formatting controls are also on the Home tab; the available dialog layout may vary by Excel release.

Conditional formatting formulas generally cannot directly use a normal reference to a different worksheet. A helper column on the same sheet avoids that limitation and makes the logic visible. For example, after the row-by-row formula has identified “Different,” select the helper cells and apply a text rule to highlight those results. You can also filter the helper column to display only “Different” or “Missing,” which is often more convenient in a long list than scanning colored cells.

For a one-time review, sort or filter the helper results rather than changing the source data. Keep the original columns intact until you have verified the comparison, especially if the workbook is a record used by others.

Handle blanks, spaces, and data types

Values that look identical on screen may not be identical underneath. A code stored as text can differ from a number with the same visible digits. Leading or trailing spaces can also cause a mismatch, and imported data may contain nonprinting characters. Before changing a comparison formula, inspect a few flagged cells and consider whether the source data needs cleaning.

  • Blank source cells: COUNTIF can treat a blank criterion as matching blank cells. If blank entries should be ignored, use a blank guard. Assuming Sheet1!A2:A100 contains the source list and the other list is ‘Sheet 2’!A2:A1000, enter in Sheet1!B2: =IF(A2="","",IF(COUNTIF('Sheet 2'!$A$2:$A$1000,A2)>0,"Found","Missing")). This leaves the result empty when A2 is blank.
  • Extra spaces: TRIM can remove ordinary leading and trailing spaces and reduce repeated internal spaces. It does not remove every possible nonbreaking space from copied web data. Cleaning a separate helper column is safer than modifying source values without review.
  • Numbers versus text: Check whether both lists store identifiers consistently. Converting a product code to a number can remove leading zeros, so do not coerce codes such as 00427 into numeric values if those zeros are meaningful.
  • Uppercase and lowercase: IF equality and COUNTIF are not case-sensitive. If the distinction matters, use a case-sensitive test rather than relying on the standard formulas.

For a case-sensitive membership check, assuming the source value is in Sheet1!A2 and the other list is ‘Sheet 2’!A2:A1000, enter =IF(SUMPRODUCT(--EXACT(A2,'Sheet 2'!$A$2:$A$1000))>0,"Found","Missing") in Sheet1!B2. EXACT compares text with case sensitivity, and SUMPRODUCT counts the TRUE results; the formula reports “Found” if there is at least one exact case-sensitive match. It can take more calculation time on very large ranges than a basic COUNTIF check.

Common comparison mistakes

The most frequent mistake is using a row-by-row formula when the lists are not aligned. If the same codes appear in different orders, “Different” may simply mean the values occupy different rows. Use COUNTIF for presence checks, or sort both lists by a reliable key before comparing corresponding rows.

Another common error is omitting quotation marks around a sheet name containing spaces, or referring to the wrong range. Check the worksheet tab name and the first and last rows in the reference list. Absolute references on the lookup range prevent it from shifting as you fill down; forgetting the dollar signs can cause later formulas to search progressively smaller or misplaced ranges.

Also remember that COUNTIF uses criteria rules: the asterisk and question mark can act as wildcards in criteria. If compared values themselves contain these characters, a COUNTIF result may not behave like a literal text comparison. For unusual identifiers, consider cleaning or escaping the criteria appropriately, or use an exact-comparison approach such as the EXACT formula above. Finally, do not conclude that two lists are identical just because every value in one is found in the other; check the reverse direction and consider duplicate counts as well.

Frequently Asked Questions

Can Excel compare two columns on different worksheets?

Yes. Use a formula that includes the other worksheet’s name, such as =IF(A2='Sheet 2'!A2,"Match","Different") for corresponding rows. If list order may differ, use COUNTIF to check whether each value appears anywhere in the other sheet’s range.

How do I compare two columns when the rows are in a different order?

Use a membership test rather than comparing row 2 with row 2. For a source value in Sheet1!A2 and a lookup range in ‘Sheet 2’!A2:A1000, =IF(COUNTIF('Sheet 2'!$A$2:$A$1000,A2)>0,"Found","Missing") reports whether that value occurs in the other list.

Does Excel’s comparison distinguish uppercase and lowercase?

Ordinary equality and COUNTIF comparisons are case-insensitive, so codes that differ only by letter case are treated as equal. Use EXACT inside SUMPRODUCT when the comparison must distinguish uppercase from lowercase.

How can I find values that are missing from either sheet?

Run a membership check in both directions: check Sheet1’s values against Sheet 2, then check Sheet 2’s values against Sheet1. Each pass identifies values missing from the other list. Review duplicates separately if counts must match too.

Why does a value look the same but return “Different”?

Possible causes include leading or trailing spaces, nonprinting characters, a number stored as text, or a meaningful leading zero. Inspect the underlying values and clean a copy or helper column before altering the original data. Standard comparisons also ignore letter case, so case alone usually does not explain a mismatch.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.