Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF STATEMENT Range | Excel Discussion (Misc queries) | |||
range in an IF statement | Excel Worksheet Functions | |||
if statement with a range without a cell reference ie, #s 1100 to | Excel Worksheet Functions | |||
IF statement (non nested) that compares cell value against range | Excel Worksheet Functions | |||
Nested IF statement with cell range reference | Excel Worksheet Functions |