To compare two sheets in Excel for matches, add a formula next to the list on the first sheet that checks whether each value appears on the second. For example, =IF(COUNTIF(Sheet2!A:A, A2)>0, "Match", "No match") marks each item in column A as found or not found on Sheet2. To pull back related data for each match, use XLOOKUP. To color the matches instead, use conditional formatting. If both sheets have the same layout, you can compare them cell by cell.
Applies to: Excel for Microsoft 365, Excel 2024, 2021, 2019 and 2016 on Windows and Mac, and Excel for the web. XLOOKUP and FILTER need Excel 2021 or later. Last reviewed October 7, 2026.
Method 1: Flag matching items with COUNTIF
Best for comparing two lists, such as customer IDs, emails or product codes, when the order is different on each sheet.
- On Sheet1, click the first empty cell next to your list, for example B2.
- Enter
=IF(COUNTIF(Sheet2!A:A, A2)>0, "Match", "No match"), changing Sheet2 and A:A to the sheet and column you’re comparing against. - Press Enter, then double-click the fill handle at the bottom-right corner of B2 to copy the formula down.
- Filter column B to show only Match or No match.
If the sheet name has spaces, put it in single quotes: 'Sales 2026'!A:A. Repeat the formula on Sheet2, pointing at Sheet1, to find items only on Sheet2.
To bring back a value for each match, such as the price from the other sheet:
=XLOOKUP(A2, Sheet2!A:A, Sheet2!C:C, "Not found")
This looks for A2 in column A of Sheet2 and returns the value from column C on the same row, or Not found. In older versions, use =IFERROR(VLOOKUP(A2, Sheet2!A:C, 3, FALSE), "Not found"). For more matching techniques, see how to match data in Excel from 2 worksheets.
Check whether the matching rows also agree
To see whether a matched item has the same price on both sheets, compare the looked-up value with your own: =IF(XLOOKUP(A2, Sheet2!A:A, Sheet2!C:C, "")=C2, "Same", "Different").
Method 3: Highlight matches with conditional formatting
- On Sheet1, select the list, for example A2:A500.
- Select Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
- Enter
=COUNTIF(Sheet2!$A:$A, A2)>0to highlight matches, or=COUNTIF(Sheet2!$A:$A, A2)=0to highlight items missing from Sheet2. - Select Format, choose a fill color and select OK twice.
Method 4: Compare two sheets cell by cell
When both sheets have the same layout, for example last month’s and this month’s version of a report:
- Add a new sheet called Compare.
- In A1, enter
=IF(Sheet1!A1=Sheet2!A1, "", "Sheet1: "&Sheet1!A1& " | Sheet2: "&Sheet2!A1). - Copy the formula across and down to cover the whole range.
Cells that match stay empty; cells that differ show both values. You can also highlight differences directly on Sheet1 with a conditional formatting rule of =A1<>Sheet2!A1.
Method 5: List all matches in one place
In Excel for Microsoft 365, FILTER can produce a list of only the matching items:
=FILTER(A2:A500, COUNTIF(Sheet2!A:A, A2:A500)>0)
Change >0 to =0 to list the items that are missing from Sheet2 instead.
Method 6: Look at both sheets side by side
- Select View > New Window to open a second window of the same workbook.
- Select View > View Side by Side.
- Show Sheet1 in one window and Sheet2 in the other. Synchronous Scrolling keeps them moving together.
This is useful for a quick visual check of short sheets.
Troubleshooting
Items that look the same show “No match”
Extra spaces or numbers stored as text are the usual cause. Clean the data with =TRIM(A2), or convert text to numbers by selecting the column and choosing Data > Text to Columns > Finish.
The formula is slow on large sheets
Replace whole-column references like A:A with exact ranges like $A$2:$A$5000.
The sheets are in different workbooks
Open both, and click into the other workbook while building the formula. Excel adds a reference such as [Prices.xlsx]Sheet1!A:A. To add up values across sheets instead, see how to add totals from different sheets in Excel.
Related: how to reference a tab name in Excel.
Related: how to do an XLOOKUP in Excel.
Related: VLOOKUP for multiple columns.
Related: do a reconciliation in Excel.
Related: highlight differences between two columns in Excel.
Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.
His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop, and more.