Forum Discussion

Smika's avatar
Smika
Copper Contributor
Nov 20, 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 su...
SharePoint Online
virendrak's avatar
virendrak
Iron 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).

  • virendrak's avatar
    virendrak
    Iron Contributor
    Nov 28, 2025

    Smika​ I was wondering if the issue has been resolved.

  • Smika's avatar
    Smika
    Copper Contributor
    Nov 26, 2025

    Thanks Virendrak, I will try that, I have also just learnt that if I change the name of the column is can mess with calculations so I am going to create new columns and see if that also helps