Forum Discussion

Smika's avatar
Smika
Copper Contributor
Nov 21, 2025

Nested formula not working

I have been using the below formula in a SharePoint Column, to find the most recent year column that 'comprehensive' is mentioned and have that year value returned to the calculated column - it is suddenly not working - or if I reset it, it works for less than a minute and then converts to only one year '2024'.  If I reduce the number of years/columns in the formula to only two it works properly and the results stay.  Any ideas what the problem is?

=IF(NOT(ISERROR(FIND("comprehensive",[2024]))),"2024",IF(NOT(ISERROR(FIND("comprehensive",[2023]))),"2023",IF(NOT(ISERROR(FIND("comprehensive",[2022]))),"2022",IF(NOT(ISERROR(FIND("comprehensive",[2021]))),"2021","No Review"))))

Thanks in advance and I am not an expert in anything IT but I am trying to use this to track work in my team.

Cheers

Karen

SharePoint Online

1 Reply

  • virendrak's avatar
    virendrak
    Brass Contributor
    Nov 21, 2025
    • SharePoint calculated columns have hidden complexity limits (formula length, nesting depth).
    • Your formula works with two years, but once you add more nested IF + FIND calls, it intermittently fails and “sticks” to the first condition (2024).

    Instead of nesting, use SEARCH() or OR() patterns to reduce depth.

    Example:

    =IF(ISNUMBER(SEARCH("comprehensive",[2024])),"2024",
 IF(ISNUMBER(SEARCH("comprehensive",[2023])),"2023",
 IF(ISNUMBER(SEARCH("comprehensive",[2022])),"2022",
 IF(ISNUMBER(SEARCH("comprehensive",[2021])),"2021","No Review"))))

    SEARCH() is more forgiving than FIND() (case‑insensitive, less error‑prone).

Resources