To check whether an ID in one sheet appears anywhere in another, put this formula in the first sheet beside the ID: with IDs in Sheet1!A2:A100 and Sheet2!A2:A100, enter =IF(COUNTIF(Sheet2!$A$2:$A$100,A2)>0,"Match","No match") in Sheet1 row 2, then fill it down. It returns “Match” when the value in A2 occurs in the second sheet’s range; use the method that fits whether you are matching IDs, whole rows, or values in the same positions.
What counts as a match?
Before comparing, decide what “match” means for your data. You might want to know whether every customer ID in a current list exists in an older list, whether the corresponding price changed, or whether two lists contain exactly the same rows. These are different checks, and a formula that answers one may not answer another.
Also decide whether order matters. If the same records appear in different row orders, compare a unique identifier across the full range rather than comparing row 2 with row 2. If the row order is meaningful and aligned, a direct cell-by-cell comparison is simpler. When there is no unique ID, you may need to compare a combination of fields, such as first name, last name, and date.
Use a copy of the workbook when the comparison could lead to edits or deletions. A comparison formula labels results; it does not prove that the underlying records are correct. Review how blanks, duplicates, spelling differences, and number formats should be treated before acting on the results.
Choose a method for your comparison
For a one-time check, formulas are usually the quickest choice. Conditional formatting is useful when you want to see matching or different cells at a glance. Power Query is better suited to repeatable comparisons of larger tables, especially when you need to produce a separate list of missing records. You can also combine methods: use a formula to flag records and filters to review the exceptions.
- Check whether an item exists: use COUNTIF for one key column.
- Compare records in the same order: compare the relevant cells row by row.
- Find a matching record and return a value: use XLOOKUP in Microsoft 365 or Excel 2021 and later.
- Compare several fields or refresh the result regularly: consider COUNTIFS or Power Query.
- Spot differences visually: apply conditional formatting to a selected range.
Excel for the web supports many common formulas, but some desktop data tools and options differ. On Mac, formulas generally use the same syntax, although ribbon placement and Power Query capabilities can vary by Excel version. The steps below describe Microsoft Excel for Microsoft 365 on Windows unless noted.
Check whether a value appears in the other sheet
COUNTIF is a straightforward way to test whether an ID or other value appears anywhere in a range. With the lookup IDs in Sheet1!A2:A100 and the comparison IDs in Sheet2!A2:A100, enter this in Sheet1!B2:
=IF(COUNTIF(Sheet2!$A$2:$A$100,A2)>0,"Match","No match")
Fill the formula down column B. If A2 contains 1042 and Sheet2’s ID range includes 1042, COUNTIF returns at least 1 and the formula displays “Match.” If the range does not contain it, the result is “No match.” The dollar signs keep the comparison range fixed as you copy the formula down, while A2 changes to A3, A4, and so on.
For a basic yes-or-no result, you can omit IF and test the count directly. With the same ranges and layout, =COUNTIF(Sheet2!$A$2:$A$100,A2)>0 returns TRUE or FALSE. A count can be more informative when duplicates matter: with the same layout, =COUNTIF(Sheet2!$A$2:$A$100,A2) returns the number of times the ID occurs in the second sheet.
Sheet names containing spaces need single quotation marks around the name. For example, if the second sheet is named “Old List,” use =IF(COUNTIF('Old List'!$A$2:$A$100,A2)>0,"Match","No match"). Excel may insert the quotation marks automatically when you select the range with the mouse while building a formula.
Compare values when the records are in a different order
When the sheets contain the same kinds of records but their rows are sorted differently, first match on a stable key such as an employee number or product code. A row-by-row comparison would give misleading results because unrelated records could occupy the same row.
XLOOKUP can find the key in the other sheet and return a related field for comparison. With product codes in Sheet1!A2:A100, product codes in Sheet2!A2:A100, and prices in Sheet2!B2:B100, enter this in Sheet1!B2 to retrieve the matching price:
=IFNA(XLOOKUP(A2,Sheet2!$A$2:$A$100,Sheet2!$B$2:$B$100),"Not found")
If A2 contains a code found on Sheet2 with a price of 19.99, the formula returns 19.99; if the code is absent, it returns “Not found.” You can then compare that result with the price in Sheet1’s own price column. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; in older Excel versions, INDEX and MATCH can provide a lookup alternative.
A key should identify one record whenever possible. If the key occurs more than once, XLOOKUP returns a result for a matching occurrence, not a complete comparison of every duplicate. In that situation, count the occurrences, add fields to make the key more specific, or use Power Query to inspect repeated records.
Compare multiple fields or whole rows
Sometimes a record is a match only if both its ID and another field agree. COUNTIFS checks multiple conditions in corresponding ranges. With IDs in Sheet1!A2:A100 and names in Sheet1!B2:B100, and the equivalent columns on Sheet2, enter this in Sheet1!C2:
=IF(COUNTIFS(Sheet2!$A$2:$A$100,A2,Sheet2!$B$2:$B$100,B2)>0,"Match","Different or missing")
If Sheet1 row 2 contains ID 1042 and the name “Lee,” the formula returns “Match” only if one row on Sheet2 contains both 1042 and “Lee” in the specified columns. If the ID exists but the name differs, the result is “Different or missing.” This is useful for distinguishing a completely missing record from a record whose details changed; you can add more criteria ranges and criteria pairs when needed.
If the sheets are already aligned row by row, a direct comparison is simpler. With the values to compare in Sheet1!A2 and Sheet2!A2, enter =IF(A2=Sheet2!A2,"Same","Different") in a result cell on Sheet1. It returns “Same” if those two cells have equal values and “Different” otherwise. Repeat for each column that matters. This does not search for the record elsewhere in Sheet2.
Highlight matches and differences visually
Conditional formatting can highlight values without adding a result column. To highlight IDs on Sheet1 that also occur on Sheet2, select Sheet1!A2:A100, then choose Home > Conditional Formatting > New Rule. Select “Use a formula to determine which cells to format,” enter the formula below, choose a format, and confirm:
=COUNTIF(Sheet2!$A$2:$A$100,A2)>0
The rule is written relative to the first selected cell, A2, so Excel adjusts it for the rest of the selected range. To highlight IDs with no match instead, use =COUNTIF(Sheet2!$A$2:$A$100,A2)=0. Choose contrasting formatting for the two rules if you want to see both categories. This changes the display, not the cell values, and the highlight updates when the underlying data changes.
Conditional formatting is helpful for review, but it is not a substitute for a clear output column when you need to filter, export, or audit the results. On Excel for Mac, the Conditional Formatting command is also on the Home tab, though the dialog layout may differ. If you cannot see formatting on a cell, check that the rule applies to the intended range and that the formula refers to its first cell correctly.
Use Power Query for repeatable comparisons
Power Query can merge tables and return records that exist in one table but not the other. It is useful when the lists are large, when the same comparison will be repeated, or when you want a refreshable report instead of formulas copied down a sheet.
- Make each data range a table by selecting it and choosing Insert > Table. Give the tables clear names and check that their column headings are consistent.
- On Windows, load each table into Power Query using Data > From Table/Range. In the Power Query Editor, check that the columns used as keys have compatible data types.
- Choose Home > Merge Queries, select the two tables, and select the matching key column in each. For a key made of multiple columns, select the columns in the same order in both tables.
- Select a join type. A Left Anti join returns rows from the first table with no matching key in the second; a Right Anti join returns rows from the second with no match in the first. An inner join returns records with matching keys in both.
- Choose Close & Load to place the result in Excel. After updating the source tables, refresh the query to run the comparison again.
Power Query compares the selected key fields; it will not automatically know that two differently named columns represent the same information. Clean and standardize the keys first, and decide how duplicates should be handled. Power Query’s availability and supported connectors vary across Mac and web versions, so the Windows desktop steps may not appear identically elsewhere.
Common problems that can hide a match
A formula can report “No match” even when two values look alike. One cell may contain a number stored as text, or an ID may include an extra space. For example, 00125 as text is not necessarily treated the same as the number 125; converting it to a number would also discard the leading zeros, which may be meaningful in an ID. Choose a consistent representation rather than applying a conversion without checking the data.
- Extra spaces: use a helper column with TRIM to remove ordinary leading and trailing spaces. TRIM does not remove every kind of nonbreaking space copied from websites.
- Different capitalization: COUNTIF is generally not case-sensitive. If capitalization is significant, use an exact, case-sensitive comparison approach such as EXACT in a row-by-row check.
- Duplicates: a “Match” only confirms at least one occurrence. Use a count or inspect the duplicate keys if each record should be unique.
- Incomplete ranges: extend the formula range when new rows are added, or use Excel tables so references can expand with the table.
- Wildcards in IDs: COUNTIF treats asterisk and question mark as wildcard characters in criteria. If IDs can contain those characters, use a method designed for literal comparison or clean the key format.
For dates, check that both columns contain real Excel dates rather than text that merely looks like a date. For currency and decimal values, consider whether tiny precision differences should count as changes. If the business rule allows a tolerance, compare the difference against that agreed threshold rather than relying on visual rounding.
Frequently Asked Questions
Can I compare two sheets in different workbooks?
Yes. Open both workbooks and build a formula that refers to the other workbook’s range, or import both tables into Power Query. External references can become harder to manage if the source workbook is moved or renamed, so keep file locations stable and verify links when reopening the comparison.
How do I find values in Sheet1 that are missing from Sheet2?
Use COUNTIF with a zero test. With IDs in Sheet1!A2:A100 and Sheet2!A2:A100, the formula =COUNTIF(Sheet2!$A$2:$A$100,A2)=0 returns TRUE when the ID in A2 does not occur in Sheet2. Fill it down and filter for TRUE, or use a Power Query Left Anti join.
Does COUNTIF distinguish uppercase and lowercase?
No. COUNTIF comparisons are not case-sensitive, so “abc” and “ABC” are treated as a match. If capitalization must differ to count as a different value, use a case-sensitive method based on EXACT and test each relevant value or row.
Can Excel compare two sheets automatically?
Formulas and conditional formatting recalculate when their source cells change, and Power Query can refresh a saved comparison. These tools do not automatically decide what constitutes a valid match; you still need to choose the correct key fields, handle duplicates, and refresh queries when appropriate.
Why does Excel say there is no match when the values look identical?
Check for spaces, text-versus-number differences, hidden characters, and inconsistent leading zeros. Compare the underlying values in the formula bar, and test cleaned helper columns if needed. Also confirm that the formula range includes the other sheet’s full data and that the sheet name and cell references are correct.
Related
- Excel Text to Columns: How to Split Data Into Fields
- Excel Text to Columns: Split Cell Text at Line Breaks
- Excel Text to Columns: Find It and Split Data Correctly
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Create a Project Schedule in Google Sheets: A Practical GuideCreate a project schedule in Google Sheets by building a task table with owners, start and finish dates,…
- Quick Schedule Maker in Excel: Build One FastTo make a quick schedule in Excel, enter a start date in B2, fill the dates across B2:H2,…
- Excel Split Cell by Space Delimiter: Methods and TipsTo split text at spaces in Excel, select the cells and choose Data > Text to Columns >…
- Wrap Text in Google Sheets: Steps, Options, and FixesTo wrap text in Google Sheets in a web browser, select the cell or range, then choose Format…
- Excel Accounting Format: How It Works and When to Use It ProperlyExcel’s Accounting number format displays monetary values with currency symbols and decimal points aligned in a column; apply…
- Excel GIS Data Analysis: Maps, Methods, and Limits ExplainedExcel can organize, check, summarize, and map geographic data, but it is not a full geographic information system…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.