SharePoint List-Calculated column to reference off 3 different columns

Harry N Nomikos 1,246 Reputation points

Hi Team,

I'd like a formula to return a value based on first column check, if the column is blank, I'd like it check the second column to see if there's a value, and if the second column is blank, I'd want it return a value based on the third column check. If the third column will be used to return the value, I'd like for the it to return the value after the colon followed by a space.

I've tried using the below formula and I'm getting syntax errors.

=IF([Inherited Sub Division]<>"",[Inherited Sub Division],IF([Originating Division]<>"",[Originating Division],TEXTAFTER([Originating Business Hierarchy],": ")))

Below is my Expected Result.

Inherited Sub Division Originating Division Originating Business Hierarchy Expected Result
Private Originating Division: Private Debt Private
Technology Technology
Consumer Consumer
Originating Division: Finance Finance
Finance Finance Audit Finance



SharePoint Server
SharePoint Server
A family of Microsoft on-premises document management and storage systems.
2,199 questions
{count} votes

Accepted answer
  1. Yanli Jiang - MSFT 21,206 Reputation points Microsoft Vendor

    Hi @Harry N Nomikos ,

    It may be that the TEXTAFTER( ) function you are using is not supported in SharePoint. You can try the following formula, I tested it and it works as you expected:

    =IF([Inherited Sub Division]<>"",[Inherited Sub Division],IF([Originating Division]<>"",[Originating Division],RIGHT([Originating Business Hierarchy],LEN([Originating Business Hierarchy])-INT(FIND(":",[Originating Business Hierarchy])))))

    This is the result:

    User's image

    User's image

    Hope this is help.

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful