Forum Discussion
Comparing two ranges of cells in an if function
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 |
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
This one works. I just have to make the True one S3.
Thank you
- 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
It could be
=IF(SUMPRODUCT(--($A$2:$R$2<>$A$3:$R$3)),"",S3)
SergeiBaklanthat would sure save a lot of typing (learned something new) and good pick up as I forgot to set it to S3!
Cheers
Damien
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
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.
