Excel Formula for Checking if Cells Match? (Guide)

Formulas & functions · Updated November 22, 2026

The simplest Excel formula for checking if two cells match is =A1=B1, which returns TRUE if the values are identical and FALSE if they are not. For a more user-friendly output, use =IF(A1=B1,"Match","No Match") to display custom text instead of TRUE/FALSE.

How the Basic Comparison Works

Excel treats the equals sign in a formula as a logical comparison operator, not just an assignment. When you write =A1=B1, Excel evaluates whether the content of cell A1 equals the content of B1. The result is a Boolean value: TRUE or FALSE. This works for numbers, text, dates, and even formulas that return values. For example, with A1 containing 5 and B1 containing 5, =A1=B1 returns TRUE. If B1 were 6, it would return FALSE. Note that text comparison is case-insensitive by default, so "apple" and "APPLE" are considered a match.

Using IF to Display Custom Results

While TRUE/FALSE is useful, most users prefer readable labels. The IF function wraps the comparison: =IF(A1=B1,"Match","No Match"). This formula checks the condition A1=B1; if true, it returns the first argument (“Match”), otherwise the second (“No Match”). You can replace those text strings with anything, such as "Same" and "Different", or even numbers. For example, with A1 as 100 and B1 as 100, the formula returns Match. If you change B1 to 99, it returns No Match.

Comparing More Than Two Cells

To check if three or more cells all match, you cannot simply chain equals signs like =A1=B1=C1 because Excel evaluates left to right, turning the first comparison into TRUE/FALSE, which then won’t equal the third cell’s text. Instead, use the AND function: =AND(A1=B1, B1=C1). This returns TRUE only if both comparisons are true. For a custom message: =IF(AND(A1=B1,B1=C1),"All Match","Not All Match"). For example, with A1=5, B1=5, C1=5, it returns All Match; change C1 to 6, and it returns Not All Match.

Case-Sensitive Matching with EXACT

If you need to distinguish between "Excel" and "excel", the EXACT function is the answer. =EXACT(A1,B1) returns TRUE only if the text is identical including case. To combine with IF: =IF(EXACT(A1,B1),"Exact Match","Different"). This is useful for IDs, passwords, or product codes where capitalization matters. Remember that EXACT only works with text; for numbers, it treats them as text but still works fine.

Comparing Entire Ranges with COUNTIF

To check if an entire column or row matches another, use COUNTIF. For instance, to see if every value in A1:A10 equals the corresponding value in B1:B10, use =COUNTIF(A1:A10,"<>"&B1:B10) as an array formula (press Ctrl+Shift+Enter in older Excel; in Microsoft 365 it works normally). This counts non-matching pairs; if it returns 0, all match. A simpler approach for a quick check is =SUMPRODUCT(--(A1:A10=B1:B10)), which returns the number of matching rows. For example, if A1:A10 has 1,2,3 and B1:B10 has 1,2,4, the SUMPRODUCT returns 2 (rows 1 and 2 match).

Dealing with Blank Cells and Errors

A common pitfall: =A1=B1 returns TRUE if both cells are blank, which might be undesirable. To treat blanks as non-matches, add a condition: =IF(OR(A1="",B1=""),"Blank",IF(A1=B1,"Match","No Match")). Also, if a cell contains an error like #N/A, the comparison returns #N/A. To avoid this, use IFERROR: =IFERROR(IF(A1=B1,"Match","No Match"),"Error"). This ensures you get a readable output even when data is missing or broken.

Using Conditional Formatting to Highlight Matches

Instead of a formula in a cell, you can visually highlight matching cells. Select the range (e.g., A1:A10), go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =A1=B1 (adjust to your layout) and set a fill color. This highlights every cell in A where the corresponding B cell matches. For the whole row, use =$A1=$B1 with the range starting at row 1. This method is dynamic—if you change a value, the highlight updates instantly.

Frequently Asked Questions

Why does =A1=B1 return FALSE when the cells look identical? This usually happens because of trailing spaces, non-printing characters, or one cell containing text while the other contains a number. Use =TRIM(A1)=TRIM(B1) to remove spaces, or =CLEAN(A1)=CLEAN(B1) to remove non-printable characters. If numbers are stored as text, compare with =VALUE(A1)=VALUE(B1).

Can I compare two columns and list the mismatches? Yes, use a helper column with =IF(A2=B2,"","Mismatch") and then filter for “Mismatch”. Or use the FILTER function in Microsoft 365: =FILTER(A2:B100, A2:A100<>B2:B100) to return only rows where the two columns differ.

Is there a formula to check if a cell matches any value in a list? Use COUNTIF: =IF(COUNTIF($D$1:$D$10,A1)>0,"In List","Not in List"). This checks if A1 appears anywhere in D1:D10. For case-sensitive, use SUMPRODUCT(--EXACT(A1,$D$1:$D$10)).

What if I want to compare two cells but ignore case and spaces? Combine functions: =IF(EXACT(TRIM(LOWER(A1)),TRIM(LOWER(B1))),"Match","No Match"). This lowercases, trims spaces, then does a case-sensitive check (which now doesn’t matter because both are lowercased).

How do I compare dates that are formatted differently? Dates are stored as numbers in Excel, so =A1=B1 works if both are real dates. If one is text, use =DATEVALUE(A1)=DATEVALUE(B1) to convert text to a date serial number before comparing.

Does the formula work in Google Sheets too? Yes, all these functions (IF, AND, EXACT, COUNTIF, SUMPRODUCT) exist in Google Sheets with the same syntax. The only difference is that array formulas in Google Sheets don’t require Ctrl+Shift+Enter; they work automatically.

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.