Nested IF

  • Thread starter Thread starter EZ
  • Start date Start date
E

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")
 
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
 
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.
 
Back
Top