Forum Discussion

grantwilliams275's avatar
grantwilliams275
Copper Contributor
Jul 29, 2024
Solved

IFS Statement with 3 Categories

I have the following table I'm trying to run an IFS statement across, to pick up the relevant status code:

 

StatusCodeList #1List 2
1.2.31.2.300
1.3.41.3.400
1.6.71.6.700
PO20#N/A0PO20
1.8.91.8.900
PO20#N/A0PO20
PE20#N/APE200
PE20#N/APE200

 

I want a formula to:

(1) Check if there is a code in column B, starting with "1" then return that code and stop checking

(2) If not, check column C for the Entry "PE20" and return that code and stop checking, or

(3) If not, check column D for the entry "PO20" and return that code and stop checking, or

(4) if none of the above then return "TBD"

 

I have been using this formula 

 

=IFS(LEFT(B11,1)="1", B11, LEFT(C11,2)="PE", C11, LEFT(D11,2)="PO", D11, "TBD")

 

I  have also tried

 

=IFS(LEFT(B11,1)="1", B11, C11="PE20", C11, D11="PO20", D11, "TBD")

 

Although it picks up any cells starting with "1" in column B correctly, it fails the checks in Columns C and D with "#N/A" even when there is correct data in there.

 

Any ideas please?

3 Replies

Resources