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.