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 su...
SharePoint Online
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