IF AND statement with cell range
 

I am trying to compare 2 cells against a range to determine if there is a
match:

Col A Col B Col C Col D
MARY BROWN TED SMITH
TED SMITH
BOB JONES

I'm using =IF(AND(C3=$A:$A,D3=$B:$B),"YES","NO") but it's obviously wrong
because I'm getting "NO" when it should return "YES". What am I doing wrong?

IF AND statement with cell range
 

See if this works for you:

=IF(SUMPRODUCT(--($A$1:$A$1000=C1),--($B$1:$B$1000=D1))0,"YES","NO")

Note that ranges used in the SUMPRODUCT function cannot be entire columns,
such as $A:$A. You could use $A$1:$A$65535.

HTH,
Elkar


"JN" wrote:

I am trying to compare 2 cells against a range to determine if there is a
match:

Col A Col B Col C Col D
MARY BROWN TED SMITH
TED SMITH
BOB JONES

I'm using =IF(AND(C3=$A:$A,D3=$B:$B),"YES","NO") but it's obviously wrong
because I'm getting "NO" when it should return "YES". What am I doing wrong?

IF AND statement with cell range
 

It works on the example but not in my spreadsheet - can you think of anything
that could be working against me?

"Elkar" wrote:

See if this works for you:

=IF(SUMPRODUCT(--($A$1:$A$1000=C1),--($B$1:$B$1000=D1))0,"YES","NO")

Note that ranges used in the SUMPRODUCT function cannot be entire columns,
such as $A:$A. You could use $A$1:$A$65535.

HTH,
Elkar


"JN" wrote:

I am trying to compare 2 cells against a range to determine if there is a
match:

Col A Col B Col C Col D
MARY BROWN TED SMITH
TED SMITH
BOB JONES

I'm using =IF(AND(C3=$A:$A,D3=$B:$B),"YES","NO") but it's obviously wrong
because I'm getting "NO" when it should return "YES". What am I doing wrong?

IF AND statement with cell range
 

I am an idiot and saw my problem. Your solution works perfectly and I truly
appreciate your help!!! Question: What do the two dashes mean?

"Elkar" wrote:

See if this works for you:

=IF(SUMPRODUCT(--($A$1:$A$1000=C1),--($B$1:$B$1000=D1))0,"YES","NO")

Note that ranges used in the SUMPRODUCT function cannot be entire columns,
such as $A:$A. You could use $A$1:$A$65535.

HTH,
Elkar


"JN" wrote:

I am trying to compare 2 cells against a range to determine if there is a
match:

Col A Col B Col C Col D
MARY BROWN TED SMITH
TED SMITH
BOB JONES

I'm using =IF(AND(C3=$A:$A,D3=$B:$B),"YES","NO") but it's obviously wrong
because I'm getting "NO" when it should return "YES". What am I doing wrong?

IF AND statement with cell range
 

Glad to help!

The two dashes are a double negative, the equivalent of multiplying by -1
twice. The SUMPRODUCT function only works with numbers. The statement
A1:A1000=C1 returns either TRUE or FALSE (not numbers). In order to get this
to work in the SUMPRODUCT, we need to convert TRUE/FALSE to numbers. Excel
considers TRUE to be equal to 1 and FALSE to be equal to 0. By multiplying
by -1, we get -1 or 0. Then multiply by -1 again, we get 1 or 0. Basically,
what Excel already knows, but now the SUMPRODUCT function can utilize the
results.

HTH,
Elkar


"JN" wrote:

I am an idiot and saw my problem. Your solution works perfectly and I truly
appreciate your help!!! Question: What do the two dashes mean?

"Elkar" wrote:

See if this works for you:

=IF(SUMPRODUCT(--($A$1:$A$1000=C1),--($B$1:$B$1000=D1))0,"YES","NO")

Note that ranges used in the SUMPRODUCT function cannot be entire columns,
such as $A:$A. You could use $A$1:$A$65535.

HTH,
Elkar


"JN" wrote:

I am trying to compare 2 cells against a range to determine if there is a
match:

Col A Col B Col C Col D
MARY BROWN TED SMITH
TED SMITH
BOB JONES

I'm using =IF(AND(C3=$A:$A,D3=$B:$B),"YES","NO") but it's obviously wrong
because I'm getting "NO" when it should return "YES". What am I doing wrong?