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.

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 ,
"Rohan" 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.

Hi!

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

AA 1
AB 2
AC 3
AD 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&B 1,F1:G7,2,0))

Biff

"Rohan" wrote in message
...
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 ,
"Rohan" 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.