# Equals function did not display True despite sting in cell 1 is the same in cell 2

#### liabilityquek

##### Member
Hi Guys

I have 5 different email fields; Email 1, 2, 3, 4 and 5

In row 1; I have "field1gmail.com", "field2gmail.com", "field3gmail.com", "field4gmail.com", "field5gmail.com" for Email 1, 2, 3, 4 and 5 respectively.

In row 2; I have "field5gmail.com","field4gmail.com", "field3gmail.com", "field2gmail.com", "field1gmail.com" for Email 1, 2, 3, 4 and 5 respectively.

I use concatenate to merge the 5 emails together.

The purpose is to identify whether is there any changes in email address regardless of the emails being place in different Email fields.

Therefore, it seems that if I were to use cell1(merge emails for row1) = cell2(merge emails for row2); the result become false.

 Email 1
 Email 2
 Email 3
 Email 4
 Email 5
 =CONCATENATE(A2,B2,C2,D2,E2)
 =CONCATENATE(A3,B3,C3,D3,E3)
 =F2=F3

#### Attachments

• 10.6 KB Views: 5

#### bosco_yip

##### Excel Ninja

Check Row 2 data is as same as Row 3

In F2, enter formula :

=ISNUMBER(SUMPRODUCT(MATCH(A2:E2,A3:E3,0)))

Regards
Bosco

#### liabilityquek

##### Member
Thanks Bosco' the formula works great!

#### Peter Bartholomew

##### Well-Known Member
Like @bosco_yip I would test first and combine results later.
= checkCopy<>baseCopy
will give TRUE for any mismatch. Because traditional formulas on the grid screw up array formulas, unless you remember CSE, I would use a defined name 'hasError' to calculate the formula (entered in the Name Manager 'Refers to' box).
To test for the presence of errors then requires
= OR(hasError)

With dynamic array formulas I can play further tricks and return a list of email addresses that have consistency errors
= FILTER( emails, hasError, "perfect" )
but that's another story.