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
- INDIRECT and ROW functions create an array of numbers, from 1 to X
- Left X characters from the two cells are compared, using equal sign
- Comparison returns TRUE or FALSE
- Two minus signs, near the start of the formula, converts TRUE and FALSE to ones and zeros
- 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
__________________________
___________________
I’m trying to compare 2 columns with Yes or No comments in them, using the IF function, but want to differentiate between the columns where the comments are Yes/Yes, Yes/No (either way) and No/No (with the results – same (True) or different (False) going into a third column. So far I’ve managed to work out how to get those that are different (Yes/No) or the same (Yes/Yes and No/No) but need to be able to separate the Yes/Yes and No/No as while they have matched responses, one is good and one is bad!
An ideas gratefully received!
PQ
Pauline, if you count the Yes comments, that might do what you need. For example, if the comments are in B2 and C2:
=COUNTIF(B2:C2,”Yes”)
Hi Debra –
How can I compare more than two cells? I used this formula but is not accurate. I don’t really need it to be case sensitive so I also remove ‘EXACT’, still didn’t work.
=AND(EXACT(B23,K23),EXACT(B23,M23),EXACT(B23,Q23),EXACT(B23,V23))
I managed to compare two cells using this formula but adding more cells gave me an error.
=IF(OR(B15=Q15, OR(B15=V15)), “Y”, “N”)
Any recommendation is greatly appreciated!
Jaime, your formula worked when I tried it. If you’re getting an unexpected result, perhaps some cells have space characters, or other hidden items.
If you don’t need case sensitivity, you could also use this:
=AND(B23=K23,B23=M23,B23=Q23,B23=V23)
Many thanks, Debra
Best wishes
PQ
Hi Debra,
I am looking to find the total number of mismatched between two texts. Your solution above will stop at the first mismatch and then consider all characters after that as mismatch. But is there a way to compare letter by letter.
In cell A1 I have Richard and cell B1 I have Rickard . So in Cell C1 i would like to 1 since only character mismatch
A1 – 123abcd456 B1 = 14xy456 C1 = 9 . Since 9 character from A1 is not matching with B1. IS there a way to do this. Please help
Use this VBA macro
Public Sub differString()
Dim a() As Byte, b() As Byte, a_$, b_$, i&, j&, d&, u&, l&, x&, y&, f&()
Const GAP = -1
Const PAD = “_”
a = [$a1].Text: b = [$b1].Text
[a3:a6].Clear
[a1:a6].Font.Name = “Courier New”
ReDim f(0 To UBound(b) \ 2 + 1, 0 To UBound(a) \ 2 + 1)
For i = 1 To UBound(f, 1)
For j = 1 To UBound(f, 2)
x = j – 1: y = i – 1
If a(x * 2) = b(y * 2) Then
d = 1 + f(y, x)
u = 0 + f(y, j)
l = 0 + f(i, x)
Else
d = -1 + f(y, x)
u = GAP + f(y, j)
l = GAP + f(i, x)
End If
f(i, j) = Max(d, u, l)
Next
Next
i = UBound(f, 1): j = UBound(f, 2)
On Error Resume Next
Do
x = j – 1: y = i – 1
d = f(y, x)
u = f(y, j)
l = f(i, x)
Select Case True
Case Err
If y = u And d >= l Or Mid$(a, j, 1) = Mid$(b, i, 1)
diag:
a_ = Mid$(a, j, 1) & a_
b_ = Mid$(b, i, 1) & b_
i = i – 1: j = j – 1
Case u > l
up:
a_ = PAD & a_
b_ = Mid$(b, i, 1) & b_
i = i – 1
Case l > u
left:
a_ = Mid$(a, j, 1) & a_
b_ = PAD & b_
j = j – 1
End Select
Loop Until i < 1 And j a Then Max = b
If c > b Then Max = c
End Function
Private Sub DecorateStrings(a$, b$, rOutA As Range, rOutB As Range, PAD$)
Dim i&, j&
FloatArtifacts a, b, PAD
FloatArtifacts b, a, PAD
rOutA = a
rOutB = b
For i = 1 To Len(a)
If Mid$(a, i, 1) Mid$(b, i, 1) Then
If Mid$(a, i, 1) PAD Then
rOutA.Characters(i, 1).Font.Color = vbRed
End If
End If
Next
For i = 1 To Len(b)
If Mid$(a, i, 1) Mid$(b, i, 1) Then
If Mid$(b, i, 1) PAD Then
rOutB.Characters(i, 1).Font.Color = vbRed
End If
End If
Next
End Sub
Private Sub FloatArtifacts(s1$, s2$, PAD$)
Dim c&, k&, i&, p&
For i = 1 To Len(s1)
c = InStr(i, s1, PAD)
If c Then
k = 0
Do
k = k + 1
If Mid$(s1, c + k, 1) PAD Then
If Mid$(s2, c, 1) = Mid$(s1, c + k, 1) Then
p = InStr(c + k, s1, PAD)
If p 0 Then
Mid$(s1, c, 1) = Mid$(s1, c + k, 1)
Mid$(s1, c + k, 1) = PAD
i = c
Exit Do
Else
i = c + k
Exit Do
End If
Else
i = c + k
Exit Do
End If
End If
If c + k > Len(s1) Then Exit Do
Loop
Else
Exit For
End If
Next
End Sub
Hello –
If I need to compare a total of 5 cells and not case sensitive. What is the best way? I tried this but it doesn’t work, I also took out ‘EXACT’
=AND(EXACT(B23,K23),EXACT(B23,M23),EXACT(B23,Q23),EXACT(B23,V23))
I was able to compare with 2 cells with this formula but adding the others gave me an error message.
=IF(OR(B17=Q17, OR(B17=V17)), “Y”, “N”)
Please help by today. Thanks in advance!
Thank you Debra!!
Another question 🙂
How would I write the formula if at least one of the cell is a match to B23, then return True? I don’t need to have all the compare cells to match in order it to be True, one match will do.
Hi Debra, please disregard my previous post as I used the formula below, it work on majority of my sheet except three cells, which return as True but it’s not accurate because B23 is Blank. I checked the format of the cells and it appear to be consistent with the others so I don’t know why these three cells result is like this. It should be False as the result.
=OR(B23=K23,B23=M23,B23=Q23,B23=V23)
Is there a way to do this?
I am looking to find the total number of mismatched between two texts. Your solution above will stop at the first mismatch and then consider all characters after that as mismatch. But is there a way to compare letter by letter.
In cell A1 I have Richard and cell B1 I have Rickard . So in Cell C1 i would like to 1 since only character mismatch
A1 – 123abcd456 B1 = 14xy456 C1 = 9 . Since 9 character from A1 is not matching with B1. IS there a way to do this. Please help
Hi Debra,
I am trying to get the correct formula for conditional formatting (highlighting cells). All cells are numeric.
In a particular column, I want to highlight all cells that are higher in value than the previous/above cell.
Can you possibly help.
PS: I know how to do conditional formatting but unable to get the correct formula.
Thanks a lot,
Raj
I want to look for certain contents in two different cells and increment them through the Columns and add 1 to the cell contents that the formula resides in.
I can get it to work for a single set using this formula
=SUM(IF( AND((L12=”1000 ms” ), (E12=”S”)),1,0))
but I’d like it to increment through a series of entries in those columns such as
=SUM(IF( AND((L12=”1000 ms” ), (E12=”S”)),1,0))
=SUM(IF( AND((L13=”1000 ms” ), (E13=”S”)),1,0))
=SUM(IF( AND((L14=”1000 ms” ), (E14=”S”)),1,0))
=SUM(IF( AND((L15=”1000 ms” ), (E15=”S”)),1,0))
Try this formula instead. The $ locks the reference to row 12, so the count will always start there.
=COUNTIFS(L$12:L12,”1000s”,E$12:E12,”S”)
Hi Debra,
It worked perfectly, can’t thank you enough! – mike
Hi,
I am trying to do regression testing of two excel files. Compare if all rows, columns are equal. Some are text, some are numeric values.
I have used sums of columns to compare numeric data, and pivot to aggregate. it has helped.
But how do I compare two columns that has text/string values.
Is there a quick way to compare using some aggregate function that ‘sums’ the entire column with text data? thought of hash function, but not sure if that is right and how would i build/use it in Excel.
There could be 20,000 rows and 40 columns with mixed numeric, string data.
Need to use it in Financial data in an Investment Bank.
Any suggestions?
I want to compare two columns in excle which has alphanumeric data.. i wnaat to compare and high light particular mismatched letter or digit.. is there any way I want to compare coloum a and b. Ex. Vinayak27 in A Colume , and vinayck27 in b volume … I what out putwith highleted mismatch in colume c…
For example :
I want to compare cell A1 with cell A3. If they are the same, cell A2 = A1
i.e. A1=3 , A3=3 then A2=3
If they are the same or LESS then the number in cell A2 is the same as in cell A1.
i.e. A1=3,2 or 1 and A3=3 then A2=A1
If the number in cell A1 is MORE than the number in cell A3 then the number in cell in A2 is increased by the difference and no more than by x2.
i.e. A1=4 and A3=3 then A2=(A3+1)=4
i.e. A1=5 and A3=3 then A2=(A3+2)=5
i.e. A1=6 or more and A3=3 then A2 is still only increased x2 =(A3+2)=5
Would be grateful for any help given ..
Hello,
hope all are safe and in good health.
i would appreciate your help with comparing amount cells and if they they match then yes if no then to give me the variance amount.
i.e. 1) A1=3, B1=3 : if the numbers match then C=Yes
i.e. 2) A2=3, B2=4: if number do not match then C=-1
Thank you
Hi there!
So… I am having issues to solve this problem regarding conditional formatting.
In a row, I want the next cell to show red if the value is lower than the previous one, and green if it is higher.
How do I compare 2 columns if they are unmatched due to sequence, for example I have 2 email addresses in column A ([email protected], [email protected]) and in column B ([email protected], [email protected]) since the email addresses and text are same but giving me result as “False” just because sequence of email address in both the columns are different. Is there any way to match this type of text in 2 columns.
How to I compare 2 textual values from 2 different cells and show a % values of the similarity.
Eg:
A1 = This is an example
B1 = This is an example
Result : 100%
A1 = For an example
B1 = This is an example
Result : 50%
i need to compare a number in 1 column to number in another column and if it is the same then produce the response “equal” – if they are not the same i need to produce the + or – difference and color code the cell. How would i write that formula?
Is there a way of identifying cells where the (numeric) value matches the value in the cell immediately above it in the column , ideally by changing formatting?
Hi,
Can anyone help me with the following question:
I’m trying to check whether two different cells have the same value, using the =A1=A2 formula.
When the cells do not have the same value, FALSE will be returned, if the same, TRUE will be returned. However, there are many cels in row 2 which are empty. Is there a possibility to return TRUE if A1 has a value and A2 is empty?
How do I compare Two Cells with characters are not in order
For Example :
A1 = 1,2,5
B1= 2,1,5
They should be count as equal
A2=3,2,5
B2=1,2,5
They should not be equal
A3=3,4,6
B3=4,6
They should be not equal