Forum Discussion

Nikoss's avatar
Nikoss
Copper Contributor
Nov 06, 2020
Solved

Need for more than 64 levels of nested - Simplify formula

Hello guys,   I am not that experienced with Excel so I need your help with this. I have a very long formula with 64 levels of nested and I need to add more which is not allowed. I will copy and pa...
admin
BI & Data Analysis
developer
excel
Excel for web
Excel on mobile
Formulas and Functions
  • mtarler's avatar
    mtarler
    Nov 09, 2020

    Nikoss I and re-attaching the sheet.  I deleted the other formulas on that sheet in case there was some confusion.  It works just fine for me.  Maybe you could explain a little more what you mean by it giving the wrong results.  Does it sometimes give correct results?  Does it ever change?  Can you give an example of a wrong result and what it gave and what you wanted it to give?

HansVogelaar's avatar
HansVogelaar
MVP
Sep 11, 2021

Balamurugan790 

You can do this with a lookup table and VLOOKUP.

See the attached version. You can extend the lookup table on Sheet2. The formulas will automatically use the extra values.

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Apr 29, 2022

    dbrauss 

    Create a lookup table and use XLOOKUP.

     

    For example,

     

     

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Apr 29, 2022

    dbrauss 

    1) Why do all conditions such as G10="None" occur twice?

    2) Why does the cell to return jump from G4 ... G30 to I31:I56?

     

    I'd enter the values None, 2TB S5X ICR, ..., 100 TB VIRT TBR in a column on the Datto sheet starting in row 4, for example in B4:B56.

    You can then use

    =XLOOKUP(G10, Datto!$B$4:$B$56, Datto!$A$4:$A$56, "")

  • dbrauss's avatar
    dbrauss
    Copper Contributor
    Apr 29, 2022
    Any chance someone could help me with my extremely long formula? I'm having the same problem.

    =IF(G10="None",Datto!G4,IF(G10="2TB S5X ICR",Datto!G5,IF(G10="2TB S5 ICR",Datto!G6,IF(G10="3TB S5 ICR",Datto!G7,IF(G10="4TB S5 ICR",Datto!G8,IF(G10="6TB S5 ICR",Datto!G9,IF(G10="8TB S5 ICR",Datto!G10,IF(G10="12TB S5 ICR",Datto!G11,IF(G10="18TB S5 ICR",Datto!G12,IF(G10="24TB S5 ICR",Datto!G13,IF(G10="36TB S5 ICR",Datto!G14,IF(G10="48TB S5 ICR",Datto!G15,IF(G10="60TB S5 ICR",Datto!G16,IF(G10="1 TB Virt ICR",Datto!G17,IF(G10="2 TB Virt ICR",Datto!G18,IF(G10="3 TB Virt ICR",Datto!G19,IF(G10="4 TB Virt ICR",Datto!G20,IF(G10="6 TB Virt ICR",Datto!G21,IF(G10="8 TB Virt ICR",Datto!G22,IF(G10="12 TB Virt ICR",Datto!G23,IF(G10="18 TB Virt ICR",Datto!G24,IF(G10="24 TB Virt ICR",Datto!G25,IF(G10="36 TB Virt ICR",Datto!G26,IF(G10="48 TB Virt ICR",Datto!G27,IF(G10="60 TB Virt ICR",Datto!G28,IF(G10="80 TB Virt ICR",Datto!G29,IF(G10="100 TB Virt ICR",Datto!G30,IF(G10="2TB S5X TBR",Datto!I31,IF(G10="2TB S5 TBR",Datto!I32,IF(G10="3TB S5 TBR",Datto!I33,IF(G10="4TB S5 TBR",Datto!I34,IF(G10="6TB S5 TBR",Datto!I35,IF(G10="8TB S5 TBR",Datto!I36,IF(G10="12TB S5 TBR",Datto!I37,IF(G10="18TB S5 TBR",Datto!I38,IF(G10="24TB S5 TBR",Datto!I39,IF(G10="36TB S5 TBR",Datto!I40,IF(G10="48TB S5 TBR",Datto!I41,IF(G10="60TB S5 TBR",Datto!I42,IF(G10="1 TB Virt TBR",Datto!I43,IF(G10="2 TB Virt TBR",Datto!I44,IF(G10="3 TB Virt TBR",Datto!I45,IF(G10="4 TB Virt TBR",Datto!I46,IF(G10="6 TB Virt TBR",Datto!I47,IF(G10="8 TB Virt TBR",Datto!I48,IF(G10="12 TB Virt TBR",Datto!I49,IF(G10="18 TB Virt TBR",Datto!I50,IF(G10="24 TB Virt TBR",Datto!I51,IF(G10="36 TB Virt TBR",Datto!I52,IF(G10="48 TB Virt TBR",Datto!I53,IF(G10="60 TB Virt TBR",Datto!I54,IF(G10="80 TB Virt TBR",Datto!I55,IF(G10="100 TB Virt TBR",Datto!I56),IF(G10="None","No Datto",IF(G10="2TB S5X ICR",Datto!A5,IF(G10="2TB S5 ICR",Datto!A6,IF(G10="3TB S5 ICR",Datto!A7,IF(G10="4TB S5 ICR",Datto!A8,IF(G10="6TB S5 ICR",Datto!A9,IF(G10="8TB S5 ICR",Datto!A10,IF(G10="12TB S5 ICR",Datto!A11,IF(G10="18TB S5 ICR",Datto!A12,IF(G10="24TB S5 ICR",Datto!A13,IF(G10="36TB S5 ICR",Datto!A14,IF(G10="48TB S5 ICR",Datto!A15,IF(G10="60TB S5 ICR",Datto!A16,IF(G10="1 TB Virt ICR",Datto!A17,IF(G10="2 TB Virt ICR",Datto!A18,IF(G10="3 TB Virt ICR",Datto!A19,IF(G10="4 TB Virt ICR",Datto!A20,IF(G10="6 TB Virt ICR",Datto!A21,IF(G10="8 TB Virt ICR",Datto!A22,IF(G10="12 TB Virt ICR",Datto!A23,IF(G10="18 TB Virt ICR",Datto!A24,IF(G10="24 TB Virt ICR",Datto!A25,IF(G10="36 TB Virt ICR",Datto!A26,IF(G10="48 TB Virt ICR",Datto!A27,IF(G10="60 TB Virt ICR",Datto!A28,IF(G10="80 TB Virt ICR",Datto!A29,IF(G10="100 TB Virt ICR",Datto!A30,IF(G10="2TB S5X TBR",Datto!A31,IF(G10="2TB S5 TBR",Datto!A32,IF(G10="3TB S5 TBR",Datto!A33,IF(G10="4TB S5 TBR",Datto!A34,IF(G10="6TB S5 TBR",Datto!A35,IF(G10="8TB S5 TBR",Datto!A36,IF(G10="12TB S5 TBR",Datto!A37,IF(G10="18TB S5 TBR",Datto!A38,IF(G10="24TB S5 TBR",Datto!A39,IF(G10="36TB S5 TBR",Datto!A40,IF(G10="48TB S5 TBR",Datto!A41,IF(G10="60TB S5 TBR",Datto!A42,IF(G10="1 TB Virt TBR",Datto!A43,IF(G10="2 TB Virt TBR",Datto!A44,IF(G10="3 TB Virt TBR",Datto!A45,IF(G10="4 TB Virt TBR",Datto!A46,IF(G10="6 TB Virt TBR",Datto!A47,IF(G10="8 TB Virt TBR",Datto!A48,IF(G10="12 TB Virt TBR",Datto!A49,IF(G10="18 TB Virt TBR",Datto!A50,IF(G10="24 TB Virt TBR",Datto!A51,IF(G10="36 TB Virt TBR",Datto!A52,IF(G10="48 TB Virt TBR",Datto!A53,IF(G10="60 TB Virt TBR",Datto!A54,IF(G10="80 TB Virt TBR",Datto!A55,IF(G10="100 TB Virt TBR",Datto!A56)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

Resources