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 21.2.31.2.3001.3.41.3.4001.6.71.6.700PO20#N/A0PO201.8.91.8.900PO20#N/A0PO20PE20#N/APE200PE20#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?
I have the following table I’m trying to run an IFS statement across, to pick up the relevant status code: StatusCodeList #1List 21.2.31.2.3001.3.41.3.4001.6.71.6.700PO20#N/A0PO201.8.91.8.900PO20#N/A0PO20PE20#N/APE200PE20#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? Read More