How to Compare Two Cells in Excel

Aside from staring at them closely, how can you compare two cells in Excel? Here are a few functions and formulas that check the contents of two cells, to see if they are the same.

Easy Way to Compare Two Cells

To compare the two cells, we’ll start with a simple check, then try more complex comparisons.

• Tip: You can see more ways to compare two cells on my Contextures site. Get an Excel workbook with all the examples from that page too.

The quickest way to compare two cells is with a formula that uses the equal sign.

• =A2=B2

If the cell contents are the same, the result is TRUE. (Upper and lower case versions of the same letter are treated as equal).

Compare Two Cells Exactly

If you need to compare two cells for contents and upper/lower case, use the EXACT function. This video shows a few EXACT examples.

As its name indicate, the EXACT function can check for an exact match between text strings, including upper and lower case.

The EXACT function doesn’t test the formatting though, so it won’t detect if one cell has some or all of the characters in bold, and the other cell doesn’t.

• =EXACT(A2,B2)

See more EXACT function examples on my Contextures site.

Partially Compare Two Cells

Sometimes you don’t need a full comparison of two cells – you just need to check the first few characters, or a 3-digit code at the end of a string.

To compare characters at the beginning of the cells, use the LEFT function. For example, check the first 3 characters:

• =LEFT(A2,3)=LEFT(B2,3)

To compare characters at the end of the cells, use the RIGHT function. For example, check the last 3 characters, and combine with the EXACT function:

• =EXACT(RIGHT(A2,3),RIGHT(B2,3))

How Much Do Cells Match?

Finally, here’s a formula from UniMord, who needed to know how much of a match there is between two cells. What percent of the string in A2, starting from the left, is matched in cell B2?

Here’s a sample list, where three formulas check the addresses in column A and B, and calculate the percent that the characters match.

Get the Text Length

The first step in calculating the percent that the cells match is to find the length of the address in column A. This formula is in cell C2:

• =LEN(A2)

Get the Match Length

Next, the formula in column D  finds how many characters, starting from the left in each cell, are a match. Lower and upper case are not compared.

• =SUMPRODUCT(
  –(LEFT(A3,
  ROW(INDIRECT(“A1:A” & C3)))
  =LEFT(B3,
  ROW(INDIRECT(“A1:A” &C3)))))

Tip: If you’re using Excel 365, there’s a shorter formula you can use, with one of the new Spill functions. See the new formula on the Compare Two Cells page of my Contextures site.

How It Works

Here’s a quick overview of how the formula works, and there are detailed notes on the Compare Two Cells page of my Contextures site

1. INDIRECT and ROW functions create an array of numbers, from 1 to X
2. Left X characters from the two cells are compared, using equal sign
3. Comparison returns TRUE or FALSE
4. Two minus signs, near the start of the formula, converts TRUE and FALSE to ones and zeros
5. SUMPRODUCT function adds up numbers. In row 5, total is 1

Get the Percent Match

The final step is to find the percent matched, by dividing the two numbers:

• =D2/C2

There is a 100% match in row 2, and only a 20% match, starting from the left, in row 5.

Thanks, UniMord, for sharing your formula to compare two cells, character by character.

Get the Compare Cells Sample File

You download an Excel workbook with all the examples, and see more ways to compare two cells on my Contextures site. The sample workbook is in xlsx format, and does not contain any macros.

That page also has details on how the Percent Matched formulas work, and there’s a shorter version of the Percent Matched formula, if you’re using Excel 365.

More Ways to Compare Two Cells

Here are a few more articles that show examples of how to compare two cells – either the full content, or partial content.

• Use INDEX, MATCH and COUNTIF to find codes within text strings. There are other formulas in the comments too, so check those out.
• Compare formulas on different sheets, with the FORMULATEXT and INDIRECT functions. Those functions are volatile though, so they’d slow down the workbook if you use too many of them.
• Be careful when using the Remove Duplicates feature in Excel – it treats real numbers and text numbers as the same value

__________________________

___________________