Excel Value Comparison Formula: Examples and Options

Formulas & functions · Updated November 19, 2026

In Excel, compare two values with =A2=B2: it returns TRUE when the values match and FALSE when they do not. To test whether they differ, use =A2<>B2. Replace A2 and B2 with the cells you want to compare; the right formula depends on whether you need an exact match, a readable label, a case-sensitive text test, or a tolerance for numbers.

What a comparison formula returns

Excel comparison formulas evaluate a condition and return the logical value TRUE or FALSE. Those results can be used directly, filtered, counted, or passed into another function. For example, TRUE can mean that two entries match, while FALSE can flag a row for review. The comparison result is not the same as a text label such as “Match”; Excel stores the result as a logical value unless you ask a formula to return text.

With a value in A2 and another in B2, the equality operator compares the cells. The not-equal operator, written as <>, reverses the test. Excel also has greater-than and less-than operators, which are useful for thresholds: for example, a value can be tested to see whether it exceeds a target. These operators compare the actual cell values, not just the way those values look on screen.

For a quick check, enter the formula in an empty cell and press Enter. If you need to check many rows, enter it in the first result row, then fill or copy it downward. Excel adjusts relative references as the formula moves, so a formula in row 3 will compare that row’s cells.

Choose the right comparison

Before writing a formula, decide what “same” means in your task. An exact comparison is appropriate for identifiers, status codes, or values that must match precisely. Text comparisons in ordinary Excel formulas are not case-sensitive, while EXACT is. Numeric comparisons may need a rounding rule or tolerance if small decimal differences should count as equal. Comparing two lists is a different task from checking corresponding cells in the same row.

  • Same row, exact value: use the equals operator.
  • Return a useful message: use IF around the comparison.
  • Text must match capitalization: use EXACT.
  • Numbers can differ slightly: compare their difference with an agreed tolerance or round them first.
  • Find whether an item appears anywhere in another list: use COUNTIF or a lookup approach rather than comparing only one row.

These distinctions prevent a common mistake: applying a valid formula to the wrong definition of a match. A comparison formula can only answer the question you encode. It cannot know whether a one-cent difference, a capitalization change, or a missing value matters to your work.

Compare cells row by row

For a worksheet with values in columns A and B, a direct comparison is usually the simplest approach. Assume the first pair is in A2 and B2; enter the formula in C2. The result is TRUE if the pair matches and FALSE otherwise. For instance, if A2 contains 42 and B2 contains 42, C2 returns TRUE; if B2 contains 43, it returns FALSE.

With the first pair of values in A2 and B2, use this formula in C2 to return a label instead of TRUE or FALSE: =IF(A2=B2,"Match","Different"). If both cells contain 42, the result is Match. If they contain 42 and 43, the result is Different. IF checks the condition first and returns its second argument when the condition is true, or its third argument when it is false.

Copy the formula down the result column to compare each row’s pair. If the formula is intended to stay beside its source data, keep the cell references relative, as shown. Excel will adjust A2 and B2 to A3 and B3 in the next row. If your comparison needs to keep a particular row or column fixed while filling, add dollar signs to the relevant reference; for example, $A2 keeps column A fixed while allowing the row number to change.

Compare text and capitalization

The equals operator treats uppercase and lowercase letters as equivalent in a standard text comparison. With A2 containing “North” and B2 containing “north”, an equality test returns TRUE. This is often helpful for ordinary names and labels, but it is unsuitable when capitalization is meaningful, such as when checking a case-sensitive code.

With the two text values to compare in A2 and B2, use =EXACT(A2,B2) to test for an exact text match including capitalization. If A2 is “North” and B2 is “north”, EXACT returns FALSE. It also returns FALSE when characters differ. EXACT is intended for text comparison; for ordinary comparisons involving numbers, dates, or mixed values, the equals operator is generally the more direct choice.

Spaces can also make text appear identical when it is not. A leading or trailing space is a character, so “North” and “North ” may fail an exact comparison. If the data may contain unwanted spaces, inspect or clean the source values rather than assuming the comparison formula is wrong. TRIM can remove ordinary leading and trailing spaces from text, but it does not remove every possible invisible character, such as some nonbreaking spaces copied from web pages.

Compare numbers with rounding or a tolerance

Numbers can display the same while retaining different underlying values. A cell formatted to show two decimal places may display 1.23 even if its stored value has more digits. The equals operator compares the stored values, not merely the formatted display. This is why a direct comparison can return FALSE for two cells that look equal.

With the numbers in A2 and B2 and a requirement to compare them to two decimal places, use =ROUND(A2,2)=ROUND(B2,2). If A2 is 1.234 and B2 is 1.233, both round to 1.23, so this test returns TRUE. Rounding is appropriate only when two decimal places are genuinely the comparison rule; it changes the values used in the test and may not fit every situation.

With numeric values in A2 and B2 and an acceptable difference of less than 0.01, use =ABS(A2-B2)<0.01. If A2 is 10 and B2 is 10.005, the absolute difference is 0.005 and the formula returns TRUE. If B2 is 10.02, the difference is 0.02 and it returns FALSE. ABS removes the sign so the test works regardless of which number is larger. Decide on the tolerance based on the meaning and precision of the data; do not select a threshold just to make a result appear to match.

Check whether a value appears in another list

A row-by-row formula checks only the two cells it references. If you need to find out whether each item in one list appears anywhere in a second list, use COUNTIF. Assume the items to check are in A2:A100 and the reference list is in B2:B100. With A2 as the item being checked, enter =COUNTIF($B$2:$B$100,A2)>0 beside it. The result is TRUE if the reference list contains at least one match and FALSE if it contains none.

To display a label instead, with the same list layout, use =IF(COUNTIF($B$2:$B$100,A2)>0,"Found","Missing"). If the value in A2 occurs once or several times in B2:B100, the result is Found; if it does not occur, the result is Missing. The dollar signs keep the search range fixed when you fill the formula down, while A2 changes to A3, A4, and so on.

COUNTIF is a convenient existence check, not a way to report every matching position or count differences between entire datasets. It also interprets the asterisk and question mark as wildcards in criteria, which can matter when those characters are part of the values you are checking. For lists containing wildcard characters as literal text, use a more deliberate comparison method or escape the characters in the criterion.

Handle blanks and missing values

Two empty cells compare as equal, so a plain equality formula may report a match even when neither cell contains useful data. That may be correct for a blank-versus-blank check, but it can be misleading in a reconciliation where blank means “not provided.” A cell containing a formula that returns an empty string can also look blank, even though it contains a formula.

With the two cells to compare in A2 and B2, use =IF(AND(A2="",B2=""),"Both blank",IF(A2=B2,"Same","Different")) when you want to distinguish two blank-looking cells from an ordinary match. If both cells are blank or return an empty string, the result is Both blank; if their values match and they are not both blank, it is Same; otherwise it is Different. If blank values should count as a mismatch, define that rule explicitly in a formula instead of relying on the default equality result.

Errors require separate attention. If a referenced cell contains an error such as #N/A, a direct comparison may return an error rather than TRUE or FALSE. Do not automatically treat every error as a difference: the error may indicate missing lookup data or another issue that needs fixing first. Inspect the source or use an error-handling formula only when you have decided what the error should mean in the comparison.

Highlight different values visually

A formula result is useful for sorting and filtering, while conditional formatting can make mismatched pairs stand out in the source range. Suppose the pairs to compare occupy A2:B100. Select A2:B100, then on Excel for Microsoft 365 for Windows choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With that selected range and its top-left cell at A2, enter =$A2<>$B2 as the rule formula and choose a format. Rows with different values will be highlighted across the selected cells.

The dollar signs lock the comparison to columns A and B while leaving the row reference flexible. That lets the rule evaluate row 2, then row 3, and so forth across the applied range. If you select only A2:A100, the same rule can highlight just the first-column cells for rows whose A and B values differ. On Mac, Conditional Formatting is also on the Home tab; the dialog wording or layout may vary slightly by Excel release. Excel for the web supports conditional formatting, though some rule-management options can differ from the desktop app.

Conditional formatting changes appearance, not the underlying values. It is therefore a visual aid rather than a stored comparison result. If you need to export, count, or filter the mismatches reliably, keep a separate formula column as well.

Common comparison problems

If the result seems wrong, check the underlying cell contents, not only their display. Number formatting can hide decimal places; dates are stored as serial values and may display differently under different formats; and text that looks numeric may not be a real number. A value imported as text can fail to compare as expected against a numeric value even though both appear to show the same digits.

  • Check for leading or trailing spaces and nonprinting characters in text.
  • Confirm whether both entries are numbers, dates, or text rather than relying on their appearance.
  • Review the number format and precision if values look equal but return FALSE.
  • Verify that a formula was filled to the intended rows and that any fixed references point to the right range.
  • Decide how blanks, errors, capitalization, and small numeric differences should be treated.

When comparing dates, ensure both cells contain valid Excel dates rather than date-looking text. When comparing formulas across sheets, make sure the references point to the intended worksheets and rows. These checks often reveal that Excel is correctly comparing different underlying values, even though the visible results seem alike.

Frequently Asked Questions

What formula compares two cells in Excel?
With the values in A2 and B2, use =A2=B2. It returns TRUE when they compare equal and FALSE when they do not.

How do I compare two columns and find differences?
With corresponding values in A2 and B2, enter =A2<>B2 in C2 and fill it down. TRUE marks rows where the two values differ; FALSE marks matching rows.

Can Excel compare text without considering capitalization?
Yes. With the text in A2 and B2, =A2=B2 is not case-sensitive. Use EXACT only when capitalization must match.

How do I compare numbers within a tolerance?
With numbers in A2 and B2 and a chosen tolerance of 0.01, use =ABS(A2-B2)<0.01. Change the threshold to suit the precision your task requires.

Why does Excel say two values differ when they look the same?
They may contain hidden decimal places, extra spaces, different data types, or a date stored as text. Check the underlying values and formatting before changing the comparison rule.

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.