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
- Excel Audit Process: Step-by-Step Guide
- Excel Audit Log Template: How to Build One
- Excel Attendance Point Tracker Template: Setup & Use
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Create an Invoice in Excel With FormulasYes: create columns for quantities and unit prices, calculate each line total, add the lines for a subtotal,…
- Count Unique Cells in Excel: Formulas and ExamplesTo count distinct, nonblank values in Excel for Microsoft 365, use =COUNTA(UNIQUE(FILTER(A2:A10,A2:A10<>""))). This assumes the values are in…
- Excel Formulas to Compare Data: Top MethodsThe fastest way to compare data in Excel is to use the IF function for row-by-row checks (e.g.,…
- Excel Sum a Range of Cells: Formulas, Tips, and FixesTo add a range of cells in Excel, use the SUM function: if the numbers are in A1…
- Concat Excel Cell Range: Join Cells With Simple FormulasWith the values to join in A2:A5, enter =CONCAT(A2:A5) to combine the range into one text string; for…
- Excel IF ISBLANK Examples: Formulas and FixesTo make Excel display one result when a cell is empty and another when it is not, combine…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.