SharePoint List- Calculated column to return 3 separate values

Question

Hi Team

I require a formula to return upto 3 separate values that come from 3 separate columns on my SharePoint List.

Below is a snipped of my expected result and I've provided the SharePoint column names

Thanks Team,
Harry

Answer 1

@Harry N Nomikos ,

Sorry for my late reply and I was just back from vocation. Thanks for your cooperation again! And now I totally get you.

I believe this is what you need:

=IF(AND([Will training be required for this change]="Yes",OR([Has BIA/HLIA been scheduled]="No",[Has BIA/HLIA been scheduled]="",[Has BIA/HLIA been scheduled]="Not yet scheduled"),[Is Comms required]="Yes"),"Training,Comms",IF(AND([Will training be required for this change]="Yes",[Has BIA/HLIA been scheduled]="Yes",[Is Comms required]="Yes"),"Training,BIA,Comms",IF(AND(OR([Will training be required for this change]="No",[Will training be required for this change]=""),[Has BIA/HLIA been scheduled]="Yes",[Is Comms required]="Yes"),"BIA,Comms",IF(AND(OR([Will training be required for this change]="No",[Will training be required for this change]=""),OR([Has BIA/HLIA been scheduled]="No",[Has BIA/HLIA been scheduled]="",[Has BIA/HLIA been scheduled]="Not yet scheduled"),[Is Comms required]="Yes"),"Comms",IF(AND([Will training be required for this change]="Yes",[Has BIA/HLIA been scheduled]="Yes",OR([Is Comms required]="",[Is Comms required]="No")),"Training,BIA",IF(AND([Will training be required for this change]="Yes",OR([Has BIA/HLIA been scheduled]="No",[Has BIA/HLIA been scheduled]="",[Has BIA/HLIA been scheduled]="Not yet scheduled"),OR([Is Comms required]="",[Is Comms required]="No")),"Training",IF(AND(OR([Will training be required for this change]="No",[Will training be required for this change]=""),[Has BIA/HLIA been scheduled]="Yes",OR([Is Comms required]="",[Is Comms required]="No")),"BIA","No Readiness activities")))))))

Answer 2

Hi @Harry N Nomikos ,

Apply the below formula to your list calculated column.

=IF(AND([Will training be required for this change]=TRUE, [Has BIA/HLIA been scheduled]=TRUE, [Is Comms required]=TRUE), "Training,BIA,Comms", IF(AND([Will training be required for this change]=TRUE, [Has BIA/HLIA been scheduled]=FALSE, [Is Comms required]=TRUE), "Training,Comms", IF(AND([Will training be required for this change]=FALSE, [Has BIA/HLIA been scheduled]=TRUE, [Is Comms required]=TRUE), "BIA,Comms", IF(AND([Will training be required for this change]=FALSE, [Has BIA/HLIA been scheduled]=FALSE, [Is Comms required]=TRUE), "Comms", IF(AND([Will training be required for this change]=TRUE, [Has BIA/HLIA been scheduled]=TRUE, [Is Comms required]=FALSE), "Training,BIA", IF(AND([Will training be required for this change]=TRUE, [Has BIA/HLIA been scheduled]=FALSE, [Is Comms required]=FALSE), "Training", IF(AND([Will training be required for this change]=FALSE, [Has BIA/HLIA been scheduled]=TRUE, [Is Comms required]=FALSE), "BIA", "")))))))

Note that the three columns ([Will training be required for this change], [Has BIA/HLIA been scheduled] and [Is Comms required]) are all Yes/No type.

Test result from my side.

---

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.