I created a calculated column in my SharePoint list and everything works fine until I add more than 20 lookup items. Is possible to create a work around to correct this issue? My formulas that work fine are below:

IF([Sample Type]="APP(EM)","4",
  IF([Sample Type]="COND (1-4 samples)","2.5",
    IF([Sample Type]="COND (Acid/Alkali/WFI set)","4",
      IF([Sample Type]="COND (EM)","4",
        IF([Sample Type]="LAL (ENDO)","3",
          IF([Sample Type]="LAL/ENDO (EM)","4",
            IF([Sample Type]="OSMO","2",
              IF([Sample Type]="PCR99","48",
                IF([Sample Type]="5068 rPA-HPLC (Protein A)","9",
                  IF([Sample Type]="pH (1-4 samples)","2.5",
                    IF([Sample Type]="pH (Acid/Alkali/WFI set)","4",
                      IF([Sample Type]="PNA23","4",
                        IF([Sample Type]="pNP (5080)","5",
                          IF([Sample Type]="SA23","12",
                            IF([Sample Type]="SKCS","24",
                              IF([Sample Type]="Titer (HPLC-Platform) Protein A","4",
                                IF([Sample Type]="TOC (EM)","4",
                                  IF([Sample Type]="TOC (Rinse: 1 sample)","2",
                                    IF([Sample Type]="TOC (Rinse: 2 samples)","3"))))))))))))))))))))
  • 1
    Break it into multiple calculated field columns
    – Erin L
    Nov 30 '16 at 19:58
  • Like Erin says.. No one is going to read this. Use a decent text editor to reformat your formula with line breaks and indentations, then indent 4 spaces so when you paste your formula here it is displayed as code Nov 30 '16 at 20:19
  • In SharePoint 2007 there was a limit of 7 nested IF statements, do not know if it was lifted in recent versions.. I would be surprised as other issues with the Calculated Column have never been fixed Dec 1 '16 at 11:07

Use concatenated or added IFs in groups of 7 (for 2007 and 2010) or 19 (for 2013 and later).

The trick for the IFs is to only nest 19 at a time and return either a text value or an empty string (""), and then concatenate another 19 nested IFs that return a text value or an empty string... repeat until done! If you are using 2007 or 2010, then nest 7 at a time, and then concatenate another 7.

            if(State="CT","Connecticut", "" ) ) ) ) ) ) ) &
            if(State="IN","Indiana", "" ) ) ) ) ) ) ) &

For nested IFs that return a number, build the first 19 IFs to return a numeric value or a zero, and the add (+) another 19 IFs, until done.

    if(State="AL", 1,
      if(State="AK", 2,
        if(State="AZ", 3,
          if(State="AR", 4,
            if(State="CA", 5,
              if(State="CO", 6,
                if(State="CT", 7,  0  ) ) ) ) ) ) )   +
    if(State="DE", 8,
      if(State="FL", 9,

Here's an example that has 50 IFs (all 50 states): http://techtrainingnotes.blogspot.com/2018/03/a-sharepoint-calculated-column-for-all.html


Remove one closing bracket from end ).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.