what's wrong with this?

  • Thread starter Thread starter dindigul
  • Start date Start date
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
 
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
 
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
 
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
 
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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
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
 
Then Try:
=IF(SUMPRODUCT((Range1=H1)*(Range2=E1)),"OK","Sorry")
Normally entered.

--
HTH

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
Back
Top