what's wrong with this?

D

dindigul

{=IF(AND(Range1=H2,Range2=E2),"OK"Sorry")}
wherein Range1 consists of 1,2,3,4 & Range2 consists of names, xx, yy, zz,
vv. H2 is 2 & E2 is yy. Still I get the result "Sorry". What's wrong I am
doing?
In Evaluating I see this
IF(AND({FALSE;TRUE;FALSE;FALSE}, {FALSE; TRUE; FALSE;FALSE}), "Ok", "Sorry")
In the next step I see
IF(FALSE, "OK","Sorry")
Obviously it's correct, but why I am not getting "OK" because AND needs both
conditions to be true to return TRUE.
Thanks
 
B

Beege

dindigul said:
{=IF(AND(Range1=H2,Range2=E2),"OK"Sorry")}
wherein Range1 consists of 1,2,3,4 & Range2 consists of names, xx, yy, zz,
vv. H2 is 2 & E2 is yy. Still I get the result "Sorry". What's wrong I am
doing?
In Evaluating I see this
IF(AND({FALSE;TRUE;FALSE;FALSE}, {FALSE; TRUE; FALSE;FALSE}), "Ok", "Sorry")
In the next step I see
IF(FALSE, "OK","Sorry")
Obviously it's correct, but why I am not getting "OK" because AND needs both
conditions to be true to return TRUE.
Thanks
Click to expand...

dindigul

Try OR instead of AND here...

Beege
 
D

dindigul

But I thought AND function needs both conditions to be true, which in this
case are true. Still why I am not getting a correct answer is the question.
Any ideas?
Thanks
 
B

Beege

dindigul said:
But I thought AND function needs both conditions to be true, which in this
case are true. Still why I am not getting a correct answer is the question.
Any ideas?
Thanks
Click to expand...

Well...

It looks like you want H2 to match all numbers in the range
simultaneously (1,2,3 and 4) which is never gonna happen. Same with E2
and the letters. The OR says if H2 matches ONE of the numbers in the
Range, that part of the formula is true. Likewise with E2.

Sound about right?

Beege
 
S

Sandy Mann

In Excel:

=AND(FALSE,FALSE) returns FALSE
=AND(TRUE,FALSE) Returns FALSE
=AND(TRUE,FALSE,TRUE,TRUE) Returns FALSE

in fact any combination containing FALSE will result in FALSE only:
=AND(TRUE,TRUE)
will return TRUE

It's a bit cludgy but

=IF(SUM(--(Range1=H1),--(Range2=E1))>1,"OK","Sorry")

returns OK for me.
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
[email protected] with @tiscali.co.uk
 
D

dindigul

Sorry, I think my question was incorrect. I am looking for a solution where
I am comparing an account number & name of person. I want both to match to
get an OK, else a Sorry. Hence this construct. I want to know why I am
getting it wrong? In H1 I supply the Name and at E1 the account number.
Elsewhere I need to get the OK or Sorry as the case may be. Now I think my Q
is clear. Now, please help. Thanks
 
Top