Forum Discussion

Cheney4206's avatar
Cheney4206
Copper Contributor
Sep 19, 2021

Nested if statements

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 ...
Formulas and Functions
HansVogelaar's avatar
HansVogelaar
MVP
Sep 19, 2021

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.

Cheney4206's avatar
Cheney4206
Copper Contributor
Sep 19, 2021
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.
  • Cheney4206's avatar
    Cheney4206
    Copper Contributor
    Sep 19, 2021
    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.
    • mathetes's avatar
      mathetes
      Gold Contributor
      Sep 20, 2021

      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.

Resources