# nested if(and) functions

1. ## nested if(and) functions

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.

2. ## Re: 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.

3. ## Re: nested if(and) functions

Hi!

Create a table that lists all the combinations and their corresponding
values:

AA 1
AB 2
AC 3
BA 2
BB 2
BC 3

Assume this table is in the range F1:G7

Then use this formula in C1:

=IF(ISNA(VLOOKUP(A1&B1,F1:G7,2,0)),"",VLOOKUP(A1&B1,F1:G7,2,0))

Biff

"Rohan" <Rohan@discussions.microsoft.com> wrote in message
news:BA8782C5-9A47-449C-83BF-F561224B9171@microsoft.com...
> 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.
>

4. ## Re: nested if(and) functions

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.

>

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1