Forum Discussion
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.
Any ideas please?
- 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")
3 Replies
- Tejas_shahCopper Contributorgrantwilliams275
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")- grantwilliams275Copper Contributor
You are a genius; that works perfectly. Thank you! Tejas_shah
- Tejas_shahCopper Contributor