Functions may only be nested 7 deep.

Instead, create a table (perhaps on a different sheet, say SHeet2,
Columns A and B) with your equivalents:


A B
1 AUDCAD AS
2 EURJPY EJ
...

etc.

Then in your original sheet, use:

=VLOOKUP(P2, Sheet2!A:B, 2, 0)

If it's possible that the value in P2 will not exist in the table, use

=IF(ISNA(MATCH(P2, Sheet2!A:A, 0)),"",VLOOKUP(P2, Sheet2!A:B, 2, 0)

to display a null string instead of the #NA! that VLOOKUP returns.


In article ,
"Bryan" wrote:

is there a limit on how many possibilities you can have in
an if statement.

i'm trying to write a statement like this.

=IF(P2="AUDCAD","AS",IF(P2="EURJPY","EJ",IF
(P2="EURGBP","GB",IF(P2="EURAUD","RA",IF
(P2="EURCHF","RZ",IF(P2="GBPJPY","SY",IF
(P2="AUDUSD","AD",IF(P2="GBPUSD","BP","")))))))

and this works fine, but when i try to add more if's i get
an this formula contains and error messege. for example if
i add one more case at the end for "CADUSD", i get an
error, but it works fine up to "GBPUSD"

=IF(P2="AUDCAD","AS",IF(P2="EURJPY","EJ",IF
(P2="EURGBP","GB",IF(P2="EURAUD","RA",IF
(P2="EURCHF","RZ",IF(P2="GBPJPY","SY",IF
(P2="AUDUSD","AD",IF(P2="GBPUSD","BP",IF
(P2="CADUSD","C1","")))))))))