Pete said:
I get the following error message when entering this formula...
"You've entered too many arguments for this function."
I need the entire formula.
=IF(AND(I3=3),6,IF(AND(I3=5),1),IF(AND(I3=7),4),IF(AND(I3="A"),7),
IF(AND(I3="C"),0),IF(AND(I3="G"),3),IF(AND(I3="K"),8)+,
F(AND(I3="R"),5),IF(AND(I3="T"),9),IF(AND(I3="X"),2,""))
Perhaps you want:
=IF(I3=3,6,IF(I3=5,1,IF(I3=7,4,IF(I3="A",7,IF(I3="C",0,
IF(I3="G",3,IF(I3="K",8,IF(I3="R",5,IF(I3="T",9,IF(I3="X",2,""))))))))))
But that is tedious to type and error-prone. Also, Excel 2003 and earlier
will complain about the number of nested functions. (No problem if you do
not care about Excel 2003 compatibility.)
Alternatively, for Excel 2007 and later, try:
=IFERROR(VLOOKUP(I3,{3,6;5,1;7,4;"A",7;"C",0;"G",3;"K",8;"R",5;"T",9;"X",2},2,FALSE),"")
Be careful with comma and semicolon separators. Reverse them if your region
uses semicolon to separate function parameter.
For Excel 2003 and earlier, ostensibly the lookup must be duplicated :-(.
You might write:
=IF(ISERROR(VLOOKUP(I3,{3,6;5,1;7,4;"A",7;"C",0;"G",3;"K",8;"R",5;"T",9;"X",2},2,FALSE))=FALSE,
VLOOKUP(I3,{3,6;5,1;7,4;"A",7;"C",0;"G",3;"K",8;"R",5;"T",9;"X",2},2,FALSE),"")
or
=IF(OR(I3={3,5,7,"A","C","G","K","R","T","X"}),
VLOOKUP(I3,{3,6;5,1;7,4;"A",7;"C",0;"G",3;"K",8;"R",5;"T",9;"X",2},2,FALSE),"")