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")



--
when u change the way u look @ things, the things u look at change.

What is the relationship of Z13 to AC9:AC28? From your contex,t ti sounds
like your wanting to look something up, but you don't say that in the
formula. I think you want:

=IF(AND(ISNUMBER(MATCH(Z13,AC9:AC28,0)),ISNUMBER(M ATCH(AA13,AF9:AF28,0)),ISNUMBER(MATCH(AB13,AG9:AG2 8,0))),"Y","N")

Note that now the formula states to find each value. If found, a number is
generated. Thus, if I get 3 numbers back, condition is met.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"EZ" wrote:

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")



--
when u change the way u look @ things, the things u look at change.

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(M ATCH(AA13,AF9:AF28,0)),ISNUMBER(MATCH(AB13,AG9:AG2 8,0))),"Y","N")

Bob Umlas
Excel MVP

"EZ" wrote in message
...
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")



--
when u change the way u look @ things, the things u look at change.

Hi,
Your not getting the answer you expect vecause your formula is incorrect and
I'm not exactly sure what your tring to do but here's a guess. You want to
test if everying in af9 - af28 = z13 etc. If so try this

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

Mike

"EZ" wrote:

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")



--
when u change the way u look @ things, the things u look at change.

Thanks Luke, but it didn't work. It shows "N" all the time even though the
value does exist in my range. Each value/condition is of the same data type
as its pertaining range. my data is numbers entered as text because of
leading zeros, so I replaced ISNUMBER with ISTEXT, but still didn't work. The
relationship is like: Lookup the state name on the state names col, then
lookup the county name in the county names col, and finally lookup the city
name in the city names col, and if all 3 matches spit out "Y", otherwise "N".
My data actually different from this example, but to make it simple.

Thanks.
--
when u change the way u look @ things, the things u look at change.


"Luke M" wrote:

What is the relationship of Z13 to AC9:AC28? From your contex,t ti sounds
like your wanting to look something up, but you don't say that in the
formula. I think you want:

=IF(AND(ISNUMBER(MATCH(Z13,AC9:AC28,0)),ISNUMBER(M ATCH(AA13,AF9:AF28,0)),ISNUMBER(MATCH(AB13,AG9:AG2 8,0))),"Y","N")

Note that now the formula states to find each value. If found, a number is
generated. Thus, if I get 3 numbers back, condition is met.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"EZ" wrote:

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")



--
when u change the way u look @ things, the things u look at change.

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.
--
when u change the way u look @ things, the things u look at change.


"Luke M" wrote:

What is the relationship of Z13 to AC9:AC28? From your contex,t ti sounds
like your wanting to look something up, but you don't say that in the
formula. I think you want:

=IF(AND(ISNUMBER(MATCH(Z13,AC9:AC28,0)),ISNUMBER(M ATCH(AA13,AF9:AF28,0)),ISNUMBER(MATCH(AB13,AG9:AG2 8,0))),"Y","N")

Note that now the formula states to find each value. If found, a number is
generated. Thus, if I get 3 numbers back, condition is met.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"EZ" wrote:

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")



--
when u change the way u look @ things, the things u look at change.