Using IF to compare ranges
 

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

Using IF to compare ranges
 

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

" wrote:

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

Using IF to compare ranges
 

Try,

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

Mike

" wrote:

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

" wrote:

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

Using IF to compare ranges
 

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

--
Biff
Microsoft Excel MVP


" wrote in
message ...
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

Using IF to compare ranges
 

Thank you, thank you!

That worked perfectly.



"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


" wrote in
message ...
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

Using IF to compare ranges
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


" wrote in
message ...
Thank you, thank you!

That worked perfectly.



"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


" wrote
in
message ...
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