Excel Compare Two Columns in Different Sheets: Methods

To compare values in two different worksheets, use COUNTIF to check whether each value on the first sheet appears anywhere in the second. If the lists are in Sheet1!A2:A100 and Sheet2!A2:A100, enter this in Sheet1!B2 and fill it down: =IF(A2="","",IF(COUNTIF(Sheet2!$A$2:$A$100,A2)>0,"Found","Missing")). It labels each nonblank value as Found or Missing, so you can identify items that do not appear on the other sheet.

Choose the kind of comparison you need

“Compare two columns” can mean a few different things. You might want to know whether names on one list occur anywhere in another list, check whether values in corresponding rows are identical, or find differences in associated details such as prices or dates. Choose the method to match the question: checking for a match anywhere is not the same as comparing row 2 with row 2.

Match anywhere: Check if each item in one column appears anywhere in the other column. Use COUNTIF or, for a returned value, XLOOKUP.
Compare corresponding rows: Compare A2 with the other sheet’s A2, A3 with A3, and so on. Use a direct comparison formula.
Review visually: Add a helper column with a result, then sort or filter it to focus on differences.
Compare larger, repeat-use lists: Consider Power Query if you need a refreshable process rather than a formula beside the data.

Before choosing, confirm that both columns contain the same kind of identifier. Comparing full names against usernames, for example, can report missing entries even when the records refer to the same person.

Find values that appear anywhere in the other sheet

The opening formula checks whether the value in Sheet1!A2 occurs in Sheet2!A2:A100. The dollar signs keep the lookup range fixed as you copy the formula down, while A2 changes to A3, A4, and subsequent rows. If the lookup value is found one or more times, the result is Found; if it is not found, the result is Missing. The formula returns a blank for an empty source cell rather than labelling it Missing.

For example, suppose Sheet1 column A contains “Maple,” “Pine,” and “Cedar,” while Sheet2 column A contains “Cedar” and “Maple.” The results on Sheet1 are Found, Missing, and Found. The order of the entries does not matter because the formula searches the full comparison range for each item.

To check the second list in the opposite direction, place the same kind of formula on Sheet2, using Sheet2’s value as the lookup value and Sheet1’s column as the search range. This two-way check matters: if one list contains an extra value, checking only the first list may not reveal that the other list has an item with no counterpart.

If a worksheet name contains spaces, put single quotation marks around it in the reference. For example, with names in Sheet1!A2:A100 and a comparison list in January Sales!A2:A100, the search range is written as 'January Sales'!$A$2:$A$100. Excel may add these quotation marks automatically when you select a range on a sheet with a spaced name.

Compare matching rows instead of searching the whole list

When row position is meaningful—such as two versions of the same ordered report—compare cells on the same row. With values in Sheet1!A2:A100 and Sheet2!A2:A100, enter this in Sheet1!B2 and fill down: =IF(AND(A2="",Sheet2!A2=""),"Both blank",IF(A2=Sheet2!A2,"Same","Different")). If both cells are empty, it reports Both blank; otherwise it reports Same or Different. For instance, if A2 contains “Maple” and Sheet2!A2 contains “Pine,” the result is Different, even if “Maple” appears in a later row on Sheet2.

Use a row-by-row comparison only when the rows are meant to correspond. If either list has been sorted differently, the formula can flag many differences that are not real missing values. In that situation, use the match-anywhere method or first align records using a reliable ID, such as an account number or product code.

A simple equality check treats uppercase and lowercase letters as equivalent. If you specifically need “ABC” and “abc” to count as different, use Excel’s case-sensitive EXACT function. With text in Sheet1!A2:A100 and the corresponding text in Sheet2!A2:A100, enter this in Sheet1!B2 and fill down: =IF(EXACT(A2,Sheet2!A2),"Same","Different"). EXACT also treats spaces as meaningful, so text that looks identical on screen can still be reported as different if one cell contains an extra space.

Show which items are missing from both lists

A comparison is more complete when you check each direction. To mark values on Sheet1 that are absent from Sheet2, use the first formula on Sheet1. Then check Sheet2 against Sheet1. With entries in Sheet2!A2:A100 and a comparison range in Sheet1!A2:A100, put this in Sheet2!B2 and fill down: =IF(A2="","",IF(COUNTIF(Sheet1!$A$2:$A$100,A2)>0,"Found","Missing")). A Missing result now identifies an item present only on Sheet2.

You can then filter each helper column to show Missing. On Windows, select a cell in the data and choose Data > Filter, then use the filter arrow in the helper-column heading to display only Missing. If the data already has filter buttons, use those instead. Keep the header row outside the formula fill range, and check that the range includes all the rows you intend to compare.

Duplicates are a separate issue from missing values. If “Maple” appears three times in Sheet1 and once in Sheet2, a basic COUNTIF check reports Found for each “Maple” row. It confirms existence, not equal quantities. If the number of occurrences matters, count each item on both sheets and compare those counts; review duplicates separately when a one-to-one match is required.

Return related information with XLOOKUP

Sometimes the goal is not just to mark a match, but to bring back a related field from the other worksheet. XLOOKUP can find a product code in one column and return its description or price from another. It is available in Microsoft 365 and Excel 2021 or later. With codes in Sheet1!A2:A100, codes in Sheet2!A2:A100, and descriptions in Sheet2!B2:B100, enter this in Sheet1!B2 and fill down: =XLOOKUP(A2,Sheet2!$A$2:$A$100,Sheet2!$B$2:$B$100,"No match"). If A2 is “P-104,” and that code’s description on Sheet2 is “Desk lamp,” the result is Desk lamp; if the code is absent, the result is No match.

XLOOKUP returns the first matching result it finds. If the search column contains duplicate codes with different descriptions, it will not show every match or warn that the code is duplicated. Check that the lookup key is unique if a single returned record is expected. For older Excel versions without XLOOKUP, the COUNTIF approach still works for a simple presence check, while INDEX and MATCH can be used for lookups with a different formula setup.

Make differences easier to review

A helper column is useful because its results can be filtered, counted, and checked. Give it a clear heading such as “Status,” fill the formula down to the last data row, and filter for Missing or Different. You can also apply conditional formatting to the helper results—for example, to make Missing stand out—without trying to format cells on one worksheet directly from a formula referring to another worksheet.

Enter the comparison formula in the helper column and fill it through the data rows.
Select the helper cells that contain results.
On Windows, choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains.
Enter Missing, choose a format, and confirm. Repeat with Different if you use that status.

Color is a visual aid, not a substitute for a result label: a status column remains understandable when printed, filtered, or viewed without the chosen formatting. The ribbon layout can vary slightly between Windows, Mac, and Excel for the web. The formulas described here work in current Microsoft 365 desktop Excel on Windows and are also supported in current Excel for Mac and Excel for the web; menu placement and some editing details can differ.

Common causes of unexpected results

If a value appears to match but Excel reports Missing, check the underlying values rather than their appearance alone. Leading or trailing spaces, different punctuation, text stored as a number, and dates with different underlying values can all affect a comparison. A cell formatted to display a date without its time, for example, can hide a time value that makes the stored values unequal.

Extra spaces: Compare the raw contents or clean imported data before matching. TRIM can remove ordinary leading, trailing, and repeated spaces from text, but it does not remove every possible nonprinting character.
Text versus numbers: The text value “00125” may not behave like the number 125, particularly when leading zeros are important identifiers. Decide which representation is correct before comparing.
Different date or number precision: Display formatting may hide decimals or times. Check the actual cell values and the required level of precision.
Blanks: A blank lookup cell needs deliberate handling. The opening formula suppresses blank source rows, but a search range containing blanks can still affect checks involving empty criteria.
Wildcards: COUNTIF criteria treat * and ? as wildcard characters. If the values themselves contain these characters and they should be matched literally, account for that behavior rather than assuming every criterion is interpreted as plain text.

Also verify that the formula points to the intended worksheet and covers the full comparison list. A range ending at row 100 will not find an item added in row 101. When the lists grow often, convert each data range into an Excel table so its range can expand with new rows; structured references can make formulas easier to maintain, though the precise formula depends on the table and column names.

Formula comparison versus Power Query

Formulas are usually the quickest choice for a one-time check or a workbook where users need to see the status beside each record. They recalculate as values change, and the helper results are easy to filter. Their main maintenance risk is a fixed range that does not include newly added rows or a formula that has not been filled down to new records.

Power Query is a useful alternative for recurring comparisons, larger datasets, or repeatable data-cleaning steps. In Excel for Microsoft 365 on Windows, open Data > Get Data to access import and transformation options, then use Power Query’s merge operation to combine tables based on a key. A left anti join can identify rows from one input with no match in the other; reversing the inputs checks the opposite direction. After setting up a query, refresh it when the source data changes. Power Query is not simply a live cell formula, and its interface and available connectors can vary by platform and Excel build.

For many ordinary worksheets, start with COUNTIF and a helper column. Move to Power Query when you need a repeatable import-and-compare workflow, or when formulas become difficult to maintain. Neither approach can determine that two different identifiers refer to the same person or item unless you provide a reliable matching key or a separate rule for resolving those records.

Frequently Asked Questions

Can I compare two columns in different Excel sheets without matching row order?
Yes. Use COUNTIF to test whether each value appears anywhere in the other sheet’s comparison range. A direct formula such as A2=Sheet2!A2 compares corresponding rows only.

How do I highlight values that are missing from the other sheet?
Create a helper column that returns Missing when COUNTIF finds no match, then filter that column or apply conditional formatting to its results. Check both directions if you need to find items unique to either list.

Does Excel distinguish uppercase and lowercase when comparing values?
Ordinary equality checks and COUNTIF comparisons are generally not case-sensitive. Use EXACT for a case-sensitive text comparison, and remember that it also treats extra spaces as differences.

Why does COUNTIF say a value is missing when it looks identical?
Check for leading or trailing spaces, text-versus-number differences, hidden date or time values, and incomplete formula ranges. The displayed formatting may make two underlying values look the same when they are not.

Can I compare lists that contain duplicates?
Yes, but a basic match check only answers whether at least one occurrence exists. It does not compare duplicate counts or establish one-to-one matches. Compare counts or use a unique key if the number of occurrences matters.

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.