Nested IF

[EZ]

I have a simple quesion about nested IF formula. I need to look up 3 values
each in a range, and if all matches show "Y" for yes otherwise "N" for no. My
formula below showed "Y" when the all 3 conditions are met, but I when I
changed one of the condition to a value that doesn't exist in the range, the
result was still "Y" instead of "N". Can someone tell me why i'm getting a
wrong answer, or whether my formula is not correct? Thanks.

=IF(((AND(Z13,AC9:AC28,AA13,AF9:AF28,AB13,AG9:AG28))),"Y","N")

 

[Bob Umlas]

WHAT 3 conditions are you talking about? Your formula has 6 RANGES, no
conditions. The AND function expects a range consisting of TRUEs or FALSEs,
not simply values. The IF statement's first parameter expects a TRUE or
FALSE condition, but you didn't put in any condition. If you wanted to see
if the value in Z13 existed in the range AC9:AC28, you need to use MATCH:
=MATCH(Z13,AC9:AC28,0) which will return a number, so to convert that to a
TRUE/FALSE you need =ISNUMBER(MATCH(Z13,AC9:AC28,0)), so perhaps what you
want is:
=IF(AND(ISNUMBER(MATCH(Z13,AC9:AC28,0)),ISNUMBER(MATCH(AA13,AF9:AF28,0)),ISNUMBER(MATCH(AB13,AG9:AG28,0))),"Y","N")

Bob Umlas
Excel MVP

 

[EZ]

Thanks Luke and Bob. It worked. The issue was with my data ranges type. the
data was coming from Access as 'general' data type with leading zeros. My
lookup values/conditions in Excel have to be forced as text in order to keep
the leading zeros, so I went ahead and changed the ranges data type from
general to text, and it worked.

Thanks.