SharePoint List-Calculated column to reference off 3 different columns

Question

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

Regards,

Harry

Answer 1

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:

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.