Excel Comparison Formulas: Functions and Examples

Formulas & functions · Updated November 19, 2026

Excel comparison formulas use operators such as =, <>, >, and <= to test values, usually returning TRUE or FALSE. To turn a test into a label, combine it with a function such as IF; use COUNTIF or COUNTIFS to compare values against a list or count records that meet conditions.

What Excel comparison formulas do

Excel does not have just one function called COMPARE. Instead, comparisons are built from comparison operators, which can be used on their own or inside other formulas. A simple comparison checks two values and returns the logical result TRUE or FALSE. A function such as IF can then use that result to display a more useful message, while COUNTIF can count cells that meet a comparison condition.

Comparisons are useful for checking whether a total meets a target, identifying differences between two columns, verifying whether an item appears on a list, and separating records into categories. They can test numbers, dates, text, and results returned by other formulas. The right approach depends on whether you need a yes-or-no answer, a label, a count, or a visual highlight.

For example, assume B2 contains 42 and B3 contains 45. The formula =B2<B3 returns TRUE because 42 is less than 45. The comparison itself does not change either value; it evaluates the relationship between them.

Basic comparison operators and examples

Excel’s comparison operators cover equality and ordering. Use the one that expresses the rule you want to test:

  • = means equal to.
  • <> means not equal to.
  • > means greater than, and < means less than.
  • >= means greater than or equal to, and <= means less than or equal to.

Assume B2 contains 42 and B3 contains 45. The formula =B2<>B3 returns TRUE, while =B2>=45 returns FALSE. In each case, the result is a logical value, not the word “yes” or “no.” A cell may display TRUE or FALSE, and another formula can use that result.

A comparison can also test a cell against a fixed value. Assume B2 contains 42. The formula =B2>=40 returns TRUE. To test text, assume C2 contains the word Paid. The formula =C2="Paid" returns TRUE. Text criteria in formulas need straight double quotation marks; cell references and numeric values do not.

Use IF, AND, and OR to return useful results

A TRUE or FALSE result is often enough for another formula, but a worksheet may need a readable status. IF checks a condition and returns one value when the condition is true and another when it is false. Assume B2 contains 42. The formula =IF(B2>=40,"Pass","Review") returns Pass. If B2 were 35, it would return Review.

AND is useful when every condition must be true. Assume B2 contains 42 and C2 contains Complete. The formula =AND(B2>=40,C2="Complete") returns TRUE because both tests are true. To return a label instead, nest AND inside IF: =IF(AND(B2>=40,C2="Complete"),"Ready","Check"). With those same values, the result is Ready.

OR returns TRUE when at least one condition is true. Assume B2 contains 42 and C2 contains Complete. The formula =IF(OR(B2>=50,C2="Urgent"),"Escalate","Standard") returns Standard because neither test is true. If C2 were Urgent, it would return Escalate even though B2 is below 50. AND and OR can contain multiple conditions, which makes them useful for rules involving several columns.

Compare text, including case-sensitive matches

For ordinary text equality tests, Excel’s equals operator is not case-sensitive. Assume A2 contains North and A3 contains north. The formula =A2=A3 returns TRUE. This is generally convenient for comparing names or categories, but it is not appropriate when uppercase and lowercase letters must count as different.

Use EXACT for a case-sensitive comparison. With North in A2 and north in A3, the formula =EXACT(A2,A3) returns FALSE. EXACT returns TRUE only when the text strings match exactly, including letter case. It compares the contents, not the appearance created by formatting.

Unexpected text mismatches can also come from spaces. A value copied from another system might contain a leading or trailing space, making it look like a match while comparing differently. Check the source data or clean it before comparing. TRIM removes ordinary extra spaces around text and reduces repeated internal spaces; it does not remove every possible nonprinting character, so imported data can require additional cleanup.

Compare two lists and find missing items

To check whether each entry in one list exists in another, COUNTIF is a straightforward option. Assume the master list is in A2:A5 and contains Pine, Cedar, Maple, and Birch. Assume C2 contains Maple. The formula =IF(COUNTIF($A$2:$A$5,C2)>0,"Found","Missing") returns Found. Copy it down beside the other entries in column C to check them against the same master list. The dollar signs keep the master range fixed as the formula is copied.

COUNTIF counts matching cells, so you can also use it without IF when the number of matches matters. With the same master list and Maple in C2, =COUNTIF($A$2:$A$5,C2) returns 1. A result of 0 means the criterion was not found. COUNTIF is not case-sensitive, and its criteria support wildcard characters such as an asterisk for multiple characters and a question mark for one character. If a list item itself contains a wildcard character, that can affect the match.

For a simple row-by-row comparison, assume A2 contains a current value and B2 contains a previous value. The formula =IF(A2=B2,"Same","Different") returns Same when the cells match under Excel’s ordinary, case-insensitive text comparison. Fill the formula down to compare subsequent rows. This checks corresponding rows; it does not search one whole list for a match anywhere in another list.

Count comparisons with COUNTIF and COUNTIFS

COUNTIF counts cells that meet one condition. For example, assume B2:B6 contains 120, 100, 95, 140, and 80. The formula =COUNTIF(B2:B6,">100") returns 2, because 120 and 140 are greater than 100. The comparison operator goes inside the quoted criteria string.

COUNTIFS counts rows that meet multiple conditions at once. Assume A2:A6 contains West, East, West, West, East, and B2:B6 contains 120, 100, 95, 140, 80. The formula =COUNTIFS(A2:A6,"West",B2:B6,">=100") returns 2: two West records have values of at least 100. Each criteria range must correspond row by row to the other criteria ranges. COUNTIFS applies AND logic, so each counted row must satisfy every specified condition.

When the comparison threshold is in a cell rather than typed into the formula, join the operator to that cell reference. Assume B2:B6 contains the five numbers above and D2 contains 100. The formula =COUNTIF(B2:B6,">="&D2) returns 3, counting 120, 100, and 140. This makes it easy to change the threshold without editing the formula.

Highlight differences with Conditional Formatting

If the goal is to spot mismatches rather than calculate a status, Conditional Formatting can color the cells that differ. On Excel for Microsoft 365 for Windows, select the range, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Set the formatting style and confirm the rule. On Mac, the Conditional Formatting controls are also on the Home tab, though the dialog layout may differ; Excel for the web offers Conditional Formatting from the Home tab as well.

Assume values to compare are in A2:B100, with one pair per row, and select A2:B100 before creating the rule. Enter =$A2<>$B2 as the rule formula. It compares the two cells in each row and highlights the selected cells when they differ. The dollar signs fix the columns while leaving the row number relative, so the rule evaluates row 2, row 3, and so on as it is applied down the range. Choose a fill color that makes mismatches easy to see without obscuring the values.

Common comparison problems and how to fix them

A formula can be logically correct and still return an unexpected result if the underlying values are inconsistent. Check these common causes before rewriting a comparison:

  • Numbers stored as text: A value that looks like 100 may be text rather than a number. Check how the data was imported and convert it to a consistent type when appropriate.
  • Hidden spaces or characters: Copied text can include spaces or nonprinting characters. Inspect the source and clean it before using an equality test.
  • Blank cells: A blank may not behave like a deliberately entered zero or empty string in every formula context. Decide whether blanks should count as missing, zero, or a match, and test that case explicitly.
  • Case sensitivity: The equals operator treats text comparisons as case-insensitive. Use EXACT if capitalization must match.
  • Wrong comparison direction: Confirm whether the rule should be greater than or greater than or equal to. A boundary value can change the result.

Dates are stored as numeric values in Excel, so date comparisons depend on the underlying date values, not just how they look on screen. If one cell contains a date with a time and another contains the same calendar date at midnight, they may not be equal because the time portion differs. Display formatting can hide that difference. Check the actual values and decide whether the comparison should include time.

Also distinguish a formula result from a displayed label. If a cell shows the text Pass because an IF formula returned it, a comparison against the original numeric score is not the same as comparing against that label. When debugging, inspect the referenced cells and test the smallest comparison by itself. This helps separate a data issue from a problem in a longer nested formula.

Frequently Asked Questions

What is the Excel formula for comparing two cells?
Use an operator between the cell references. For example, =A2=B2 returns TRUE if the values compare as equal and FALSE if they do not. Use <> in place of equals to test whether they differ.

How do I compare two columns and show a result?
Assuming the values to compare are in A2 and B2, use =IF(A2=B2,"Match","No match"). Fill the formula down to compare each corresponding row. Excel’s normal text equality comparison is not case-sensitive.

How can I find values in one list that are missing from another?
Assuming the reference list is in A2:A100 and the value to check is in C2, use =IF(COUNTIF($A$2:$A$100,C2)=0,"Missing","Found"). Copy it down to check more values. COUNTIF does not distinguish uppercase from lowercase.

How do I make an Excel comparison case-sensitive?
Use EXACT with the two cells. For example, assuming the text is in A2 and B2, =EXACT(A2,B2) returns TRUE only when their text matches exactly, including capitalization.

Can Excel highlight cells that do not match?
Yes. Select the comparison range and create a formula-based rule through Home > Conditional Formatting > New Rule. For two columns beginning in A2 and B2, use =$A2<>$B2 to highlight rows where the paired values differ.

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.