Forum Discussion
Smika
Nov 21, 2025Copper Contributor
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...
virendrak
Nov 21, 2025Brass Contributor
- 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).