nested if(and) functions

I would do something like:

J K L M N
1 a b c d
2 a 1 2 3 3
3 b 2 2 3 4
4 c 3 3 4 4


The use something like:

=VLOOKUP(A1,J:N,MATCH(B1,J1:N1,FALSE),FALSE)




In article <BA8782C5-9A47-449C-83BF-F561224B9171@microsoft.com>,
"Rohan" <Rohan@discussions.microsoft.com> wrote:

> Hi,
>
> I need to create a function that can cope with more than 7 if ands, where it
> can read what's in cell A1 and what's in cell B1 then spit out a defined
> value in C1. For example,
>
> If A1="a" and B1="a", then 1, but if A1="a" and B1="b", then 2 and so on for
> combinations aa, ab, ac, ad, ae, ba, bb, bc..........ea, eb, ec, ed and ee.
> My formula below works but only gets me up to bc.
>
> =IF(AND(A1="a",B1="a"),"1",IF(AND(A1="a",B1="b"),"2",IF(AND(A1="a",B1="c"),"3"
> ,IF(AND(A1="a",B1="d"),"3",IF(AND(A1="b",B1="a"),"2",IF(AND(A1="b",B1="b"),"2"
> ,IF(AND(A1="b",B1="c"),"3",)))))))
>
> I've found some tricks to get around the 7-limit IF functions but can't seem
> to make them work for my nest IF(AND) function.
>
> I hope this makes sense. Any help would be most appreciated.
>
> thanks.
>
> Rohan.

beautiful. Thanks very much.

"JE McGimpsey" wrote:

> I would do something like:
>
> J K L M N
> 1 a b c d
> 2 a 1 2 3 3
> 3 b 2 2 3 4
> 4 c 3 3 4 4
>
>
> The use something like:
>
> =VLOOKUP(A1,J:N,MATCH(B1,J1:N1,FALSE),FALSE)
>
>
>
>
> In article <BA8782C5-9A47-449C-83BF-F561224B9171@microsoft.com>,
> "Rohan" <Rohan@discussions.microsoft.com> wrote:
>
> > Hi,
> >
> > I need to create a function that can cope with more than 7 if ands, where it
> > can read what's in cell A1 and what's in cell B1 then spit out a defined
> > value in C1. For example,
> >
> > If A1="a" and B1="a", then 1, but if A1="a" and B1="b", then 2 and so on for
> > combinations aa, ab, ac, ad, ae, ba, bb, bc..........ea, eb, ec, ed and ee.
> > My formula below works but only gets me up to bc.
> >
> > =IF(AND(A1="a",B1="a"),"1",IF(AND(A1="a",B1="b"),"2",IF(AND(A1="a",B1="c"),"3"
> > ,IF(AND(A1="a",B1="d"),"3",IF(AND(A1="b",B1="a"),"2",IF(AND(A1="b",B1="b"),"2"
> > ,IF(AND(A1="b",B1="c"),"3",)))))))
> >
> > I've found some tricks to get around the 7-limit IF functions but can't seem
> > to make them work for my nest IF(AND) function.
> >
> > I hope this makes sense. Any help would be most appreciated.
> >
> > thanks.
> >
> > Rohan.
>