How to Compare Two Cells in Excel? (Exact/Partial Match)

When you have a lot of data in a spreadsheet it becomes quite difficult to compare the contents of two cells without just staring closely into the screen.

Moreover, if your data pans over a large number of rows, it can be quite annoying to compare data in cells that are quite far apart.

In this tutorial we will show you how to compare two cells in Excel, in different cases:

When you want to compare for an exact match (case insensitive)
When you want to compare for an exact match (case sensitive)
When you want to display a defined value for a match or no match
When you want to compare for a partial match
When you want to compare numeric values in the cells

Table of Contents

How to Compare Two Cells for an Exact Match (Case Insensitive)

This method is probably the quickest way to compare two cells in Excel (for equality).

The formula used in this method is really simple, involving only a comparison operator, in this case, the ‘equal to’ operator.

Say you have two values in cells B1 and B2, and you want to find out if they are an exact match or not.

Simply type the following formula to return a TRUE (if they match) or FALSE (if they don’t).

=B1=B2

Please note that this formula does not consider whether the contents of the cells are in upper or lowercase. Both are considered the same.

If the contents of both cells match exactly (irrespective of case), the formula returns a TRUE. Else, it returns a FALSE.

How to Compare Two Cells for an Exact Match (Case Sensitive)

If you want an exact match, including the case of the text, then you should use the EXACT function instead of the comparison operator.

The EXACT function takes two strings and checks for an exact match, including whether the text is in upper or lower case.

The syntax for the function is simple:

=EXACT(text1, text2)

Here, text1 and text2 are the two strings that we want to compare.

The function compares the two strings and returns a TRUE value if there is an exact match (including case), and FALSE if there isn’t.

In the following example, the EXACT function returns a FALSE, since the strings in cells B1 and B2 don’t match (although both cells have the same text, the value in B1 starts with an uppercase letter, while the one in B2 starts with a lowercase letter).

How to Display a Defined value for a Match or No Match

The above two methods simply return a TRUE or FALSE value after testing for a match.

However, someone else looking at your worksheet might not be able to understand what you mean by TRUE or FALSE.

So you might want to return a more descriptive value if there’s a match.

Moreover, you might want to display some specific text for a match in some cases.

For example, you might want to display the text ‘Winning’ if values in cells B1 and B2 are equal. If not then you can probably display “Not winning”, or something like that.

For this, you can use the IF function as follows:

=IF(B1=B2,"Winning","Not winning")

The above formula uses the IF function to compare values in B1 and B2. If the condition “B1=B2” is TRUE, the function returns the text “Winning”. If not, it returns the text “Not winning”.

For an exact match (including case), you can use the formula:

=IF(EXACT(B1,B2),"Winning","Not winning")

IF formula with Exact to compare two cells

How to Compare Two Cells for a Partial Match

If you don’t necessarily need an exact match, you can also check for a partial match between two cells.

For example, you might want to see if the last few digits of a phone number or the first few digits of an ID are the same.

To compare the first n digits, you can use the following formula:

=LEFT(string1,n)=LEFT(string2,n)

So, for example, if you want to compare the first 2 digits of values in B1 and B2, then n=2, and your formula will be:

=LEFT(B1, 2) = LEFT(B2, 2)

Compare part of a cell with another cell

Similarly, to compare the last n digits, you can use the following formula:

=RIGHT(string1,n)=RIGHT(string2,n)

So, for example, if you want to compare the last 3 digits of values in B1 and B2, then n=3, and your formula will be:

=RIGHT(B1, 3) = RIGHT(B2, 3)

Note: If you want an exact match of the first n digits (including case), then you can wrap an EXACT function around the LEFT or RIGHT functions as follows:

=EXACT(LEFT(B1, 2),LEFT(B2,2))

EXACT with LEFT formula to compare two cells

You can also compare two cells to see if the value in one of them exists in the other.

This can be achieved using wildcard characters like ‘*’ or ‘?’. For example, consider the following pair of values:

Let’s say you want to check if the two values (in B1 and B2) match partially (one of the values exists as a part of the other value).

The formula for this is a little more complex since you will need to consider cases where the first value is smaller than the second and vice-versa:

=IF(LEN(B1)<LEN(B2),IFERROR(MATCH("*"&B1&"*",B2:B2,0)>0,FALSE),IFERROR(MATCH("*"&B2&"*",B1:B1,0)>0,FALSE))

Formula to compare partial strings in two cells

Explanation of the Formula

Let us break down this formula and analyze it part by part:

LEN(B1)<LEN(B2)

We want to know if there is a partial match between values in B1 and B2. This means, that if the string in B1 is shorter than the string in B2, then we want to know if B1 exists in B2.

Similarly, if it’s the other way round, we want to find out if the value in B2 exists in B1.

To find out which string is shorter, we simply compare the lengths of the two strings using the LEN function.

MATCH(“*”&B1&”*”,B2:B2,0)>0

If the string in B1 is shorter than that of B2, then we want to check if the string of B1 is a part of the string in B2.

The MATCH function here checks if the value in B1 exists as a part of B2 (using the asterisk wildcard).

If it does, then the MATCH function returns the position of the matching string in B2. If it does not exist in B2, then the function returns a 0.

But we just want to know whether the value exists in B2 or not. We don’t really care about the position. So, we simply check if the value returned is more than 0.

If it is, that means that the value in B1 is a part of the value in B2. We got a partial match.

The only problem is that if there is no match, the MATCH function returns an #N/A error. To keep this in check, we can simply wrap an IFERROR function around this formula, like this:

IFERROR(MATCH(“*”&B1&”*”,B2:B2,0)>0,FALSE)

So now if the value in B1 does not exist in B2, the IFERROR function simply returns a FALSE.

In the same way, if the string in B2 is shorter than that of B1, then we want to check the other way round.

We simply exchange cell references B1 and B2, so the third parameter (of the main IF function) is specified as:

IFERROR(MATCH(“*”&B2&”*”,B1:B1,0)>0,FALSE)

To sum up, in the above formula, we simply use the IF function to compare LEN(B1) and LEN(B2). If the string in B1 is shorter, the function returns IFERROR(MATCH(“*”&B1&”*”,B2:B2,0)>0,FALSE).

Otherwise, it returns IFERROR(MATCH(“*”&B2&”*”,B1:B1,0)>0,FALSE)

Let us try this on a few sample pairs of strings to see if the formula actually works in all cases:

Checking in content of one cell is there in another

As we can see, the formula compares the two values in each column and returns a TRUE whenever there is a partial match, and a FALSE when there’s no match.

Also read: Excel If Statement with Multiple Conditions Range

How to Compare Two Cells to Find the Larger or Smaller Number

Finally, let us see a case where you have numeric values in two different cells, and you want to compare them to see if one of them is larger than the other.

For example, the following dataset contains pairs of values in rows 1 and 2, and we want to find out which value is larger.

The formula you can use is as follows:

=IF(B1>B2,B1,B2)

If you just want to know if the value in B2 is larger than the one in B1, then your formula will be:

=B2>B1

Simple arithmetic formula to compare cells

In this tutorial, we showed you how to compare two cells in a variety of cases.

We tried to cover as many comparison possibilities as we could think of. We hope from the cases we mentioned here you were able to find what you needed.

Other Excel tutorials you may also find helpful: