Compare Two Excel Sheets and Find Matching Values Easily

To find values from one Excel sheet in another, use COUNTIF and fill the formula down. With IDs in Sheet1!A2:A6 and Sheet2!A2:A6, enter =IF(COUNTIF(Sheet2!$A$2:$A$6,A2)>0,"Match","No match") in Sheet1!B2; if A2 contains an ID listed on Sheet2, the result is Match. For larger or more complex comparisons, choose a method based on whether you need to check one column, entire rows, or differences between datasets.

What counts as a match?

A match can mean several things, and deciding which one you need prevents misleading results. The simplest case is whether a value in one column also appears anywhere in a column on the other sheet. For example, you might check whether every product code in a current inventory list appears in a supplier list. The matching values do not have to be on the same row.

A row-by-row comparison asks a different question: does the value in row 2 on one sheet equal the value in row 2 on the other? That is useful when both sheets have the same order and structure, such as two versions of a report. If rows have been sorted differently, a row-by-row comparison can show false differences even when the records are present in both sheets.

You can also match records using more than one field. An employee number may not be unique across separate systems, for instance, so you might require both an employee number and a department to match. Finally, a comparison may need to identify values found only on one sheet, duplicates, or records whose associated details changed. Establish the goal before choosing a formula or tool.

Choose a comparison method

For a quick check of one column, COUNTIF is simple and works in older Excel versions as well as Microsoft 365. Use conditional formatting when you mainly want to see matching cells highlighted. XLOOKUP is useful when a match should return related information from the other sheet, such as a product description or status. Power Query is better suited to repeatable comparisons involving large tables, several columns, or recurring monthly files.

Find whether a value exists: COUNTIF can return a label or a count.
See matches on the sheet: Conditional formatting highlights matching cells without adding a results column.
Return related data: XLOOKUP searches for a key and brings back a value from another column.
Compare or combine whole tables: Power Query can merge tables using one or more matching columns.

These methods answer related but not identical questions. A formula result of Match says that a value exists in the specified range; it does not prove that the full record is identical, that it appears only once, or that every column agrees.

Find matching values with COUNTIF

COUNTIF checks how many cells in a range meet a condition. With IDs in Sheet1!A2:A6 and Sheet2!A2:A6, put this in Sheet1!B2 and copy it down:

=IF(COUNTIF(Sheet2!$A$2:$A$6,A2)>0,"Match","No match")

If A2 is 1042 and 1042 appears in Sheet2!A2:A6, COUNTIF returns a count greater than zero and the formula displays Match. If the ID is absent, it displays No match. The dollar signs keep the lookup range fixed as you fill the formula downward, while A2 changes to A3, A4, and so on. Extend the range to cover all of your records, or use an Excel Table so the range can expand as rows are added.

To see the number of occurrences instead of a label, with the same ID columns and the value to check in A2, use =COUNTIF(Sheet2!$A$2:$A$6,A2). If the ID appears twice in the second sheet, the result is 2. This is useful for spotting duplicates, but it counts occurrences in the lookup range, not unique records.

Sheet names with spaces need single quotation marks in a reference. For example, with lookup IDs in a sheet named Supplier List, the range reference is 'Supplier List'!$A$2:$A$6. When checking another workbook, both files may need to be open while you build the reference; Excel can create an external link, but links can become inconvenient if a file is moved or renamed.

Match records using multiple columns

If a single key is not enough, COUNTIFS can test several conditions at once. Suppose Sheet1 has an ID in column A and a region in column B, and Sheet2 stores those same fields in columns A and B. With the record to check in Sheet1 row 2, enter this in C2:

=IF(COUNTIFS(Sheet2!$A$2:$A$6,A2,Sheet2!$B$2:$B$6,B2)>0,"Match","No match")

If A2 contains 1042 and B2 contains West, the result is Match only if a row on Sheet2 has both 1042 in column A and West in column B. A row with the same ID but a different region does not qualify. Copy the formula down to check the other records. Each criteria range must cover the same number of rows.

This approach is often safer than joining fields into a single text key. Concatenation can create collisions: ID 12 and region 3 produce the same joined text as ID 1 and region 23 if no clear separator is used. If you do construct a combined key, use a delimiter that cannot occur in the source values, or use a method such as COUNTIFS that tests fields separately.

Highlight matching cells visually

Conditional formatting is convenient when you want to inspect matches directly rather than create a separate results column. On Sheet1, select the cells to check, such as A2:A100. In Excel for Microsoft 365 on Windows, choose Home > Conditional Formatting > New Rule, select “Use a formula to determine which cells to format,” and enter:

=COUNTIF(Sheet2!$A$2:$A$100,A2)>0

Choose a format, such as a fill color, and confirm the rule. The formula assumes the checked IDs are in Sheet1!A2:A100 and the reference list is in Sheet2!A2:A100. A2 is relative, so Excel checks each selected row in turn; the lookup range is fixed. A value such as 1042 is highlighted if it appears in the reference range.

For the reverse check, create a corresponding rule on Sheet2 using the Sheet1 range as the lookup range. Conditional formatting changes appearance, not the underlying values, and it may be harder to audit than a formula column when a workbook has many rules. Excel for Mac has the Conditional Formatting controls on the Home tab too, though dialogs can look different. Excel for the web also supports conditional formatting, with some differences in rule editing.

Return details with XLOOKUP

Use XLOOKUP when the goal is not merely to label a value as present, but to retrieve information from its matching record. Suppose Sheet1!A2:A6 contains product IDs, while Sheet2!A2:A6 contains those IDs and Sheet2!B2:B6 contains their statuses. Enter this in Sheet1!B2:

=XLOOKUP(A2,Sheet2!$A$2:$A$6,Sheet2!$B$2:$B$6,"Not found")

If A2 is 1042 and the corresponding status on Sheet2 is Open, the formula returns Open. If the ID is absent, it returns Not found. XLOOKUP uses exact matching by default, which is usually what is wanted for IDs. It is available in Microsoft 365 and Excel 2021 or later; older Excel versions may need a combination of INDEX and MATCH or another approach. XLOOKUP returns the first match it finds, so duplicate IDs can hide additional records.

Compare whole tables with Power Query

Power Query is a useful alternative when comparisons happen repeatedly or involve many rows. It can import tables, standardize or transform data, and merge two datasets using matching columns. Unlike a worksheet formula, a query can be refreshed after the source tables are updated. It is not a live cell-by-cell comparison: the results reflect the source data when the query is refreshed.

In Excel for Microsoft 365 on Windows, format each dataset as a table using Insert > Table, then use Data > Get Data > From Other Sources or the available workbook import option to load the tables into Power Query. In the Power Query Editor, choose Home > Merge Queries, select the two tables, and click the corresponding key column in each. To match on multiple fields, select the same set of columns in the same order in both previews, using Ctrl-click on Windows. Choose a join kind to control which records are retained.

Inner join: keeps rows with a match in both tables.
Left anti join: keeps rows from the first table that have no match in the second.
Right anti join: keeps rows from the second table that have no match in the first.

After merging, expand the nested table column if you need fields from the other dataset, then choose Home > Close & Load. The exact import options depend on where the source files are stored. Excel for Mac and Excel for the web may offer different Power Query capabilities and interface options; if a specific connector or merge control is missing, use the desktop Windows version or check the features available in your edition.

Common matching problems

Two cells that look the same may not contain the same underlying value. A number stored as text, a leading or trailing space, a nonbreaking space copied from a webpage, or a different date value can affect a comparison. Check cell formats and inspect representative values before assuming a formula is wrong. Formatting a text ID as a number does not always convert its stored value; use a deliberate conversion when the data types differ.

For simple extra spaces and nonprinting characters, with the source text in A2, this cleanup formula returns a cleaned version in B2:

=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))

If A2 contains “North” followed by a nonbreaking space, the formula returns North without that trailing character. TRIM removes excess ordinary spaces, CLEAN removes certain nonprinting characters, and SUBSTITUTE changes the common nonbreaking space to a regular space first. This does not solve every data-quality issue; for example, differences in punctuation or spelling may need a separate rule.

Other frequent causes of confusing results include incomplete ranges, duplicate keys, and unexpected blanks. A formula that checks only rows 2 through 100 will miss a match in row 101. COUNTIF can also treat wildcard characters such as an asterisk as pattern characters in criteria, which matters if the values themselves contain such characters. When duplicates matter, count occurrences or inspect the source table instead of treating any positive count as proof of a one-to-one match.

Check differences and keep results reliable

To compare two sheets row by row, the records must be aligned in the same order. With values in Sheet1!A2:A6 and Sheet2!A2:A6, enter =IF(A2=Sheet2!A2,"Same","Different") in Sheet1!B2 and fill down. If A2 and Sheet2!A2 both contain 1042, the result is Same. This checks only the paired cells; it does not search the second sheet for the value or compare other columns. Sort order changes can therefore make this method unsuitable.

For an auditable result, keep the original source sheets unchanged, add a clearly named result column, and record what fields define a match. Filter the result column to show No match, then verify a few cases manually. If you use formulas, check that the ranges include all rows and that the formulas have filled down consistently. Save a separate copy before making bulk cleanup or replacement changes.

Frequently Asked Questions

Can I compare two sheets in different Excel workbooks?
Yes. Open both workbooks and build a formula that refers to the lookup range in the other workbook, or import both files into Power Query. External workbook references can depend on file locations and names, so keep the source file accessible and verify links if it is moved.

How do I find values that are in one sheet but not the other?
Use the COUNTIF formula to label each value Match or No match, then filter for No match. Repeat the check in the opposite direction if you also need values exclusive to the second sheet. A one-way check does not reveal omissions in the reverse direction.

Does COUNTIF find duplicate matches?
COUNTIF returns the number of matching cells. A result above 1 indicates repeated matching entries in the searched range, but it does not identify which full records are duplicates. Add other criteria with COUNTIFS or inspect the matched rows if uniqueness matters.

Why does Excel say there is no match when the values look identical?
Check for spaces, nonprinting characters, text-versus-number differences, and inconsistent date values. Also confirm that the lookup range includes the row containing the expected value. A cleanup step or consistent data conversion may be needed before comparing.

Can I compare entire rows instead of one column?
Yes. If records have a reliable unique key, use that key to locate the corresponding record and compare the other fields, or merge tables in Power Query using multiple columns. Comparing row 2 with row 2 alone is only reliable when both sheets have the same row order.

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.