Nested if statements

%3CLINGO-SUB%20id%3D%22lingo-sub-2765709%22%20slang%3D%22en-US%22%3ENested%20if%20statements%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2765709%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%20I%20am%20in%20desperate%20need%20of%20help.%20I%20am%20pulling%20my%20hair%20out.%20I%20am%20trying%20to%20do%20a%20nested%20if%20statement%20with%2049%20if's%20from%20a%20drop%20down%20list%20and%20it%20keeps%20saying%20the%20formula%20is%20missing%20an%20opening%20or%20closing%20parenthesis%20and%20when%20I%20add%20them%20it%20says%20I%20have%20too%20many%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2765709%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2765811%22%20slang%3D%22en-US%22%3ERe%3A%20Nested%20if%20statements%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2765811%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1160543%22%20target%3D%22_blank%22%3E%40Cheney4206%3C%2FA%3E%20You%20can%20probably%20replace%20the%20nested%20IFs%20with%20a%20VLOOKUP%20or%20XLOOKUP%20formula.%3C%2FP%3E%0A%3CP%3ELet's%20say%20you%20want%20to%20do%20this%3A%3C%2FP%3E%0A%3CP%3EIF%20A1%3D1%20THEN%20%22one%22%2C%20IF%20A1%3D2%20THEN%20%22two%22%2C%20IF%20A1%3D3%20THEN%20%22three%22%2C%20etc.%20etc.%3C%2FP%3E%0A%3CP%3EYou'd%20create%20a%20table%20like%20this%20in%20for%20example%20K1%3AL50%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2250%25%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%0A%3CTD%20width%3D%2250%25%22%20height%3D%2230px%22%3Eone%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2250%25%22%20height%3D%2230px%22%3E2%3C%2FTD%3E%0A%3CTD%20width%3D%2250%25%22%20height%3D%2230px%22%3Etwo%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2250%25%22%20height%3D%2230px%22%3E3%3C%2FTD%3E%0A%3CTD%20width%3D%2250%25%22%20height%3D%2230px%22%3Ethree%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2250%25%22%20height%3D%2230px%22%3E4%3C%2FTD%3E%0A%3CTD%20width%3D%2250%25%22%20height%3D%2230px%22%3Efour%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20height%3D%2230px%22%3E...%3C%2FTD%3E%0A%3CTD%20height%3D%2230px%22%3E...%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20then%20use%3C%2FP%3E%0A%3CP%3E%3DVLOOKUP(A1%2C%20K1%3AL50%2C%202%2C%20FALSE)%3C%2FP%3E%0A%3CP%3Eto%20look%20up%20the%20value%20of%20A1%20in%20column%20K%20and%20return%20the%20corresponding%20value%20from%20column%20L.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2765818%22%20slang%3D%22en-US%22%3ERe%3A%20Nested%20if%20statements%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2765818%22%20slang%3D%22en-US%22%3EI%20can't%20get%20it%20to%20let%20me%20send%20the%20formula.%20What%20I%20am%20trying%20to%20do%20is%20have%20the%20person%20select%20from%20a%20drop%20down%20list%20with%2049%20options.%20Depending%20on%20which%20one%20they%20pick%20it%20will%20do%20a%20different%20math%20problem.%20For%20example%20if%20you%20choose%20the%20fifth%20one%20down%20on%20the%20list%20it%20will%20multiply%20cell%20j8%20times%20300.235.%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi everyone I am in desperate need of help. I am pulling my hair out. I am trying to do a nested if statement with 49 if's from a drop down list and it keeps saying the formula is missing an opening or closing parenthesis and when I add them it says I have too many

 

5 Replies
=IF(G4=Sheet2!A2,J8*56.901,IF(G4=Sheet2!A3,J8*61.316,IF(G4=Sheet2!A4,J8*67.448,IF(G4=Sheet2!A5,J8*85.842,IF(G4=Sheet2!A6,J8*91.973,IF(G4=Sheet2!A7,J8*98.105,IF(G4=Sheet2!A8,J8*122.632,IF(G4=Sheet2!A10,J8*147.158,IF(G4=Sheet2!A11,J8*153.289,IF(G4=Sheet2!A13,J8*183.949,IF(G4=Sheet2!A14,J8*187.013,IF(G4=Sheet2!A15,J8*196.211,IF(G4=Sheet2!A16,J8*202.343,,IF(G4=Sheet2!A17,J8*208.474,IF(G4=Sheet2!A18,J8*233.000,IF(G4=Sheet2!A19,J8*245.265,IF(G4=Sheet2!A20,J8*282.053,IF(G4=Sheet2!A21,J8*294.316,IF(G4=Sheet2!A22,J8*374.029,IF(G4=Sheet2!A23,J8*392.423,IF(G4=Sheet2!A24,J8*42.185,IF(G4=Sheet2!A25,J8*52.732,IF(G4=Sheet2!A26,J8*84.370,IF(G4=Sheet2!A27,J8*105.464,IF(G4=Sheet2!A28,J8*126.557,IF(G4=Sheet2!A29,J8*158,195,IF(G4=Sheet2!A30,J8*168.742,IF(G4=Sheet2!A31,J8*263.659,IF(G4=Sheet2!A32,J8*28.697,IF(G4=Sheet2!A33,J8*57.391,IF(G4=Sheet2!A34,J8*71.740,IF(G4=Sheet2!A35,J8*111.962,IF(G4=Sheet2!A36,J8*152.678,IF(G4=Sheet2!A37,J8*178.123,IF(G4=Sheet2!A38,J8*103.011,IF(G4=Sheet2!A39,J8*190.324,IF(G4=Sheet2!A40,J8*93.812,IF(G4=Sheet2!A41,J8*187.627,IF(G4=Sheet2!A44,J8*156.356,IF(G4=Sheet2!A45,J8*166.780,IF(G4=Sheet2!A46,J8*208.474,IF(G4=Sheet2!A47,J8*234.533,IF(G4=Sheet2!A48,J8*239.745,IF(G4=Sheet2!A49,J8*250.169,IF(G4=Sheet2!A50,J8*317.924,IF(G4=Sheet2!A51,J8*56.901,IF(G4=Sheet2!A52,J8*98.105,IF(G4=Sheet2!A53,J8*202.343,IF(G4=Sheet2!A54,J8*245.265,)

@Cheney4206 You can probably replace the nested IFs with a VLOOKUP or XLOOKUP formula.

Let's say you want to do this:

IF A1=1 THEN "one", IF A1=2 THEN "two", IF A1=3 THEN "three", etc. etc.

You'd create a table like this in for example K1:L50:

 

1 one
2 two
3 three
4 four
... ...

 

You can then use

=VLOOKUP(A1, K1:L50, 2, FALSE)

to look up the value of A1 in column K and return the corresponding value from column L.

I can't get it to let me send the formula. What I am trying to do is have the person select from a drop down list with 49 options. Depending on which one they pick it will do a different math problem. For example if you choose the fifth one down on the list it will multiply cell j8 times 300.235.
let me see if it will allow me to do it thi=IF(G4=Sheet2!A2,J8*56.901,IF(G4=Sheet2!A3,J8*61.316,IF(G4=Sheet2!A4,J8*67.448,IF(G4=Sheet2!A5,J8*85.842,IF(G4=Sheet2!A6,J8*91.973,IF(G4=Sheet2!A7,J8*98.105,IF(G4=Sheet2!A8,J8*122.632,IF(G4=Sheet2!A10,J8*147.158,IF(G4=Sheet2!A11,J8*153.289,IF(G4=Sheet2!A13,J8*183.949,IF(G4=Sheet2!A14,J8*187.013,IF(G4=Sheet2!A15,J8*196.211,IF(G4=Sheet2!A16,J8*202.343,,IF(G4=Sheet2!A17,J8*208.474,IF(G4=Sheet2!A18,J8*233.000,IF(G4=Sheet2!A19,J8*245.265,IF(G4=Sheet2!A20,J8*282.053,IF(G4=Sheet2!A21,J8*294.316,IF(G4=Sheet2!A22,J8*374.029,IF(G4=Sheet2!A23,J8*392.423,IF(G4=Sheet2!A24,J8*42.185,IF(G4=Sheet2!A25,J8*52.732,IF(G4=Sheet2!A26,J8*84.370,IF(G4=Sheet2!A27,J8*105.464,IF(G4=Sheet2!A28,J8*126.557,IF(G4=Sheet2!A29,J8*158,195,IF(G4=Sheet2!A30,J8*168.742,IF(G4=Sheet2!A31,J8*263.659,IF(G4=Sheet2!A32,J8*28.697,IF(G4=Sheet2!A33,J8*57.391,IF(G4=Sheet2!A34,J8*71.740,IF(G4=Sheet2!A35,J8*111.962,IF(G4=Sheet2!A36,J8*152.678,IF(G4=Sheet2!A37,J8*178.123,IF(G4=Sheet2!A38,J8*103.011,IF(G4=Sheet2!A39,J8*190.324,IF(G4=Sheet2!A40,J8*93.812,IF(G4=Sheet2!A41,J8*187.627,IF(G4=Sheet2!A44,J8*156.356,IF(G4=Sheet2!A45,J8*166.780,IF(G4=Sheet2!A46,J8*208.474,IF(G4=Sheet2!A47,J8*234.533,IF(G4=Sheet2!A48,J8*239.745,IF(G4=Sheet2!A49,J8*250.169,IF(G4=Sheet2!A50,J8*317.924,IF(G4=Sheet2!A51,J8*56.901,IF(G4=Sheet2!A52,J8*98.105,IF(G4=Sheet2!A53,J8*202.343,IF(G4=Sheet2!A54,J8*245.265,)s way.

but it's always J8 times something, is that right?

In that case you can still use the suggestion from Hans Vogelaar to accomplish the desired result with far less pain. Whatever is selected from that drop down is used as a reference in the VLOOKUP with a formula like

=J8 * VLOOKUP(dropdownselection,TableOfValues,2,0)

ALL OF THE TEXTBOOKS on Excel warn against trying to nest too many IFs in a single formula; it becomes (as you have discovered) utterly unwieldy, to say nothing of unintelligible, un-maintainable...etc.