Comparing two ranges of cells in an if function

[Joseph Assaf]

I have two rows of address information that I have to compare.  If cells A-R in row 2 are identical to cells A-R in row three then I want cell S inn row three two be returned if true and blank if false.  I tried the below formula and it gives me a VALUE error.

=IF(A2:R2=A3:R3,S3,"")

It works if I dont compare ranges, for example if I only compare A2 & A3.  How do I do this formula with the logical test as comparing ranges of cells in rows.

First Name

Last Name

Grad Year

Grade Level Description

Dob

Address Line 1

Address Line 2

City

State Short

Postal Code/ Zip

District

Resides With

Address Line 11

Address Line 21

Address Line 3

City1

State Short1

Postal Code/ Zip1

Parent/Guardian First

Parent/Guardian Last

Jim

Smith

2018

12

1/1/2001

123 Any Street

x

Any City

State

10000

ABC

TRUE

123 Any Street

x

x

Any City

State

10000

William

Smith

#VALUE!

Jim

Smith

2018

12

1/1/2001

123 Any Street

x

Any City

State

10000

ABC

TRUE

123 Any Street

x

x

Any City

State

10000

Mary

Smith

[#2]

Hi Joseph

I am sure I'll think of a better way to do this but for now this will work:

=IF(AND(A2=A3, B2=B3, C2=C3,D2=D3,E2=E3,F2=F3,G2=G3,H2=H3,I2=I3,J2=J3,K2=K3,L2=L3,M2=M3,N2=N3,O2=O3,P2=P3,Q2=Q3,R2=R3), "True", "False")

No matter what you change from A2 to R3, the formula will pick it up.

Nice and simple.

Cheers

Damien

[#3]

It could be

=IF(SUMPRODUCT(--($A$2:$R$2<>$A$3:$R$3)),"",S3)

[#4]

@Sergei Baklanthat would sure save a lot of typing (learned something new) and good pick up as I forgot to set it to S3!

Cheers

Damien

[#5]

This one works.  I just have to make the True one S3.

Thank you

[#6]

I tried this one, because it looks much easier than above but it always returns the true value even when I check it and I know its not.  It looks like it should work.  (Unless sumproduct function counts everything as a number value and doesnt work well with text- I dont know enough about it)

Thank you though.  I do appreciate the help

[#7]

Hi Joseph,

Yes, SUM ignores text and logical values, as well as SUMPRODUCT when it sums multiplication of the arrays.

In our case we have no one text value returning to SUMPRODUCT, we have only logical values TRUE or FALSE as result of cells comparasion. Logical values also don't work, thus with double dash we convert TRUE and FALSE in 1 and 0 accordingly (same result if we multiply resulting array on 1).

Finally SUMPRODUCT receives only numeric 1/0 array.

Back to your sample - in attached the formula works correctly.

[#8]

Hi Joseph

Thanks for the feedback. I'm simple on solutions where possible. No matter which option you run with from the suggestions, it's important that you are happy with they result and can work with the formula moving forward.

Glad we were able to give you a couple of options.

If your query is resolved, I would suggest that you flag the best response so this topic is closed for other users future reference of what we have discussed.

Best wishes on your Excel journey!

Cheers
Damien