Nested IF error

%3CLINGO-SUB%20id%3D%22lingo-sub-1628605%22%20slang%3D%22en-US%22%3ENested%20IF%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1628605%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%20I%20have%20a%20problem%20here%20I%20don't%20know%20how.%20The%20last%20if%20seems%20to%20be%20problematic.%20When%20I%20use%20IF%205%20times%20its%20OK%20but%20when%20I%20add%20the%20last%20one%20it%20won't%20let%20me%20finish%20the%20formula.%20I'm%20assigning%20a%20value%20based%20on%20the%20string%20(a-c)%20and%20if%20the%20row%20is%20odd%20or%20even.%20I%20have%20a%20list%20of%20some%20values%20for%20example%20abcbaba%20and%20next%20to%20it%20the%20Q-P%205-7%20table%20of%20values%20to%20assign.%20I%20don't%20know%20if%20it%20is%20enough%20info%2C%20I%20would%20have%20to%20remake%20the%20table%20(translate%2C%20remove%20sensible%20data...)%3C%2FP%3E%3CP%3E%3DIF(AND(C5%3D%22a%22%3BISEVEN(ROW()))%3B%24Q%245%3B%3C%2FP%3E%3CP%3EIF(AND(C5%3D%22a%22%3BISODD(ROW()))%3B%24P%245%3B%3C%2FP%3E%3CP%3EIF(AND(C5%3D%22b%22%3BISEVEN(ROW()))%3B%24Q%246%3B%3C%2FP%3E%3CP%3EIF(AND(C5%3D%22b%22%3BISODD(ROW()))%3B%24P%246%3B%3C%2FP%3E%3CP%3EIF(AND(C5%3D%22c%22%3BISEVEN(ROW()))%3B%24Q%247%3B%3C%2FP%3E%3CP%3EIF(AND(C5%3D%22c%22%3BISODD(ROW()))%3B%24P%247%3B%22error%22)))))%20(error%20here%20to%20let%20me%20know%20i%20wrote%20bad%20string%20value)%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1628605%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1628662%22%20slang%3D%22en-US%22%3ERe%3A%20Nested%20IF%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1628662%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F779891%22%20target%3D%22_blank%22%3E%40momoos1_-650%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou're%20missing%20the%20last%20closing%20parenthesis%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(AND(C5%3D%22a%22%2CISEVEN(ROW()))%2C%24Q%245%2CIF(AND(C5%3D%22a%22%2CISODD(ROW()))%2C%24P%245%2CIF(AND(C5%3D%22b%22%2CISEVEN(ROW()))%2C%24Q%246%2CIF(AND(C5%3D%22b%22%2CISODD(ROW()))%2C%24P%246%2CIF(AND(C5%3D%22c%22%2CISEVEN(ROW()))%2C%24Q%247%2CIF(AND(C5%3D%22c%22%2CISODD(ROW()))%2C%24P%247%2C%22error%22))))))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20a%20shorter%20version%20of%20the%20formula%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(C5%3D%22a%22%2CIF(ISODD(ROW())%2C%24P%245%2C%24Q5)%2CIF(C5%3D%22b%22%2CIF(ISODD(ROW())%2C%24P%246%2C%24Q%246)%2CIF(C5%3D%22c%22%2CIF(ISODD(ROW())%2C%24P%247%2C%24Q%247)%2C%22error%22)))%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi! I have a problem here I don't know how. The last if seems to be problematic. When I use IF 5 times its OK but when I add the last one it won't let me finish the formula. I'm assigning a value based on the string (a-c) and if the row is odd or even. I have a list of some values for example abcbaba and next to it the Q-P 5-7 table of values to assign. I don't know if it is enough info, I would have to remake the table (translate, remove sensible data...)

=IF(AND(C5="a";ISEVEN(ROW()));$Q$5;

IF(AND(C5="a";ISODD(ROW()));$P$5;

IF(AND(C5="b";ISEVEN(ROW()));$Q$6;

IF(AND(C5="b";ISODD(ROW()));$P$6;

IF(AND(C5="c";ISEVEN(ROW()));$Q$7;

IF(AND(C5="c";ISODD(ROW()));$P$7;"error"))))) (error here to let me know i wrote bad string value)

Thank you for your help

2 Replies

@momoos1_-650 

You're missing the last closing parenthesis:

 

=IF(AND(C5="a",ISEVEN(ROW())),$Q$5,IF(AND(C5="a",ISODD(ROW())),$P$5,IF(AND(C5="b",ISEVEN(ROW())),$Q$6,IF(AND(C5="b",ISODD(ROW())),$P$6,IF(AND(C5="c",ISEVEN(ROW())),$Q$7,IF(AND(C5="c",ISODD(ROW())),$P$7,"error"))))))

 

Here is a shorter version of the formula:

 

=IF(C5="a",IF(ISODD(ROW()),$P$5,$Q5),IF(C5="b",IF(ISODD(ROW()),$P$6,$Q$6),IF(C5="c",IF(ISODD(ROW()),$P$7,$Q$7),"error")))

@momoos1_-650 

As variant

=INDEX(
   $Q$5:$R$7,
   1*($C$5="a")+2*($C$5="b")+3*($C$5="c"),
   MOD(ROW(),2)+1)