What is the workaround for limit in nested if statements in sharepoint calculated column?

Question

I have to create a list wherein when user inputs a data to a column the corresponding calculated column should take the data and analyze the condition and then return the value, but my SharePoint online is giving me error when I try to add more than 12 nested if conditions, any workaround please suggest?

Update : This is my formula :

=IF([pn]="S.Vish,ab","[email protected]",IF([pn]="S.Ash,","[email protected]",IF([pn]="S.Kav,ab","[email protected]",IF([pn]="S.Sun,ab","[email protected]",IF([pn]="S.Urb,ab","[email protected]",IF([pn]="S.John,ab","[email protected]",IF([pn]="S.Paw,ab","[email protected]",IF([pn]="S.Ram,","[email protected]",IF([pn]="S.Din","[email protected]",IF([pn]="S.Moh,","[email protected]",IF([pn]="S.Man","[email protected]",IF([pn]="S.Vik,","[email protected]",IF([pn]="S.Vij",,"[email protected]",IF([pn]="S.Pram,ab","[email protected]",IF([pn]="S.Ank,ab","[email protected]",IF([pn]="S.Sai,ab","[email protected]",IF([pn]="S.Ano,ab","[email protected]",IF([pn]="S.Ars,ab","[email protected]",IF([pn]="S.She,ab","[email protected]",IF([pn]="S.Gov,","[email protected]",IF([pn]="S.Nal","[email protected]",IF([pn]="S.San,","[email protected]",IF([pn]="S.Sun","[email protected]","Not OK"))

Answer 1

A late answer for clarification:

• Maximum nest IFs: SharePoint 2007 and 2010 only allow 7. SharePoint 2013 and later (including Online) allows 19. Larger values are possible by using batches of 7 or 19 or less and concatenating or adding the results.
• Maximum formula length: SharePoint 2007 and 2010 only allow 1024 characters. (2013 and later is around one billion!)

The following is works in 2013 and later by using batches of 19 or less. It won't work in 2007 and 2010, even in batches of 7, due to the total length.

if(State="AL","Alabama",
if(State="AK","Alaska",
if(State="AZ","Arizona",
if(State="AR","Arkansas",
if(State="CA","California",
if(State="CO","Colorado",
if(State="CT","Connecticut",
if(State="DE","Delaware",
if(State="FL","Florida",
if(State="GA","Georgia",
if(State="HI","Hawaii",
if(State="ID","Idaho",
if(State="IL","Illinois",
if(State="IN","Indiana",
if(State="IA","Iowa",
if(State="KS","Kansas",
if(State="KY","Kentucky",
if(State="LA","Louisiana",
if(State="ME","Maine",""))))))))))))))))))) &
if(State="MD","Maryland",
if(State="MA","Massachusetts",
if(State="MI","Michigan",
if(State="MN","Minnesota",
if(State="MS","Mississippi",
if(State="MO","Missouri",
if(State="MT","Montana",
if(State="NE","Nebraska",
if(State="NV","Nevada",
if(State="NH","New Hampshire",
if(State="NJ","New Jersey",
if(State="NM","New Mexico",
if(State="NY","New York",
if(State="NC","North Carolina",
if(State="ND","North Dakota",
if(State="OH","Ohio",
if(State="OK","Oklahoma",
if(State="OR","Oregon",
if(State="PA","Pennsylvania",""))))))))))))))))))) &
if(State="RI","Rhode Island",
if(State="SC","South Carolina",
if(State="SD","South Dakota",
if(State="TN","Tennessee",
if(State="TX","Texas",
if(State="UT","Utah",
if(State="VT","Vermont",
if(State="VA","Virginia",
if(State="WA","Washington",
if(State="WV","West Virginia",
if(State="WI","Wisconsin","")))))))))))

Answer 2

Break up your one Calculated Column into multiple Calculated Columns, then reference the (sub) Calculated Columns in your final result (Calculated Column)

Answer 3

I don't think having more than 12 nested IFs is the problem. Are you grouping your IFs in groups of 7? I have a test list where I added 13 IFs statements and it works.

Answer 4

As a workaround, we can create a single text column to instead of the calculated column, then use SharePoint designer workflow to set the value to the column. SharePoint Designer provide a Graphic User Interface to edit the logic of a workflow, it will be more comfortable to work in a SharePoint Designer workflow if you may have complex logic.

The links below about SharePoint Designer would be helpful:

https://docs.microsoft.com/en-us/sharepoint/dev/general-development/workflow-actions-quick-reference-sharepoint-workflow-platform

https://docs.microsoft.com/en-us/sharepoint/dev/general-development/creating-a-workflow-by-using-sharepoint-designer-and-the-sharepoint-wo