Using IF to compare ranges

[rhodesmk]

This seems like it should be a simple task, however, I have entered the
following formula:
=IF(A4:I4=K4:S4," ","CHECK")
to compare a range of cells to another range (current data vs draft changed
data). If there's a difference, I want the word "CHECK" to appear in this
cell. In the formula dialog box, the formula result is right, exactly what I
want. But when I click OK to apply this formula, the cell in which the
formula rests contains the dreaded "#VALUE!" message.

Advice??

Thanks,
rhodesmk

 

[rhodesmk]

Sorry, I meant to also state that some cells contain numbers, but others
contain text, if that makes a difference.

 

[Mike H]

Try,

=IF(SUMPRODUCT(--(A4:I4=K4:S4))=9,"Check","")

Mike

 

[T. Valko]

Try this array formula** :

=IF(AND(A4:I4=K4:S4),"","Check")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

This does a cell-to-cell comparison.

A4=K4
B4=L4
C4=M4
etc
etc
I4=S4

 

[rhodesmk]

Thank you, thank you!

That worked perfectly.

Try this array formula** :

=IF(AND(A4:I4=K4:S4),"","Check")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

This does a cell-to-cell comparison.

A4=K4
B4=L4
C4=M4
etc
etc
I4=S4

 

[T. Valko]

You're welcome. Thanks for the feedback!