SOLVED

# IFS Statement with 3 Categories

Copper Contributor

# 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:

 Status Code List #1 List 2 1.2.3 1.2.3 0 0 1.3.4 1.3.4 0 0 1.6.7 1.6.7 0 0 PO20 #N/A 0 PO20 1.8.9 1.8.9 0 0 PO20 #N/A 0 PO20 PE20 #N/A PE20 0 PE20 #N/A PE20 0

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.

3 Replies
best response confirmed by grantwilliams275 (Copper Contributor)
Solution

# Re: IFS Statement with 3 Categories

@grantwilliams275
Hi Kindly use the below formula.
=IFS(LEFT(IFNA(B2,2),1)="1",B2,LEFT(C2,2)="PE",C2,LEFT(D2,2)="PO",D2,TRUE,"TBD")

# Re: IFS Statement with 3 Categories

You are a genius; that works perfectly. Thank you! @Tejas_shah

# Re: IFS Statement with 3 Categories

@grantwilliams275
If you got the solution please like my post and mark as a best response.

Thank you

1 best response

Accepted Solutions
best response confirmed by grantwilliams275 (Copper Contributor)
Solution

# Re: IFS Statement with 3 Categories

@grantwilliams275
Hi Kindly use the below formula.
=IFS(LEFT(IFNA(B2,2),1)="1",B2,LEFT(C2,2)="PE",C2,LEFT(D2,2)="PO",D2,TRUE,"TBD")