I made a workable formula using nested IF statements, but I still need to add some additional logic to it. I would be nesting extra statements inside the formula, and I'm not entirely sure how to approach that.

The current formula is:

```=IF(LEFT(G3,1)="1","998",IF(LEFT(G3,1)="2","998",IF(LEFT(G3,1)="3","998",IF(LEFT(G3,1)="4","996",IF(LEFT(G3,1)="5","996",K3)))))
```

This is working fine, but now I need to add the additional logic:

• If G3 begins with "9" and K3 does not begin with "075", "076", or "089" then cell equals "997"

My failed attempt was something like this:

```IF(LEFT(G3,1)="1","998",IF(LEFT(G3,1)="2","998",IF(LEFT(G3,1)="3","998",IF(LEFT(G3,1)="4","996",IF(LEFT(G3,1)="5","996",IF(LEFT(G3,1)="9"(AND(LEFT(K3,3)<>"075"))K3))))))
```

With this being the attempted nested statement I added:

`IF(LEFT(G3,1)="9"(AND(LEFT(K3,3)<>"075"))`

For "076" and "089," I would then need to repeat this process a few more times, but I have yet to get it right for just one of these.

I am aware that some VBA code may be used to achieve this, but for this file, I'd like to stay away from VBA and stick to formulas exclusively.

Any help would be appreciated.

Nov 21, 2022 in Others 669 views

Use this:

`=IF(SUMPRODUCT(--(LEFT(G3,1)={"1","2","3"}))>0,"998",IF(SUMPRODUCT(--(LEFT(G3,1)={"4","5"}))>0,"996",IF(AND(SUMPRODUCT(--(LEFT(G3,1)="9"))>0,SUMPRODUCT(--(LEFT(K3,3)={"075","076","089"}))=0),"997")))`

I hope this helps you.

• 63,700 points

Combining MID and LEFT Functions in Excel 2019

I currently have a course name and ...READ MORE

Excel If Functions with Multiple Conditions

Use this: =IF(C3<=0,"Green",IF(C3<500,"Silver",IF(C3<=1499,"Gold","Platinum"))) I added <=0 just in case you have ...READ MORE

Excel Formula Help Conditional Formatting - If A2 =TODAY and B2=Y turn A2 Green. If B2 = N turn A2 RED

Create a conditional format rule on A2 ...READ MORE

INDEX formula in Excel, Top 10, repeats previous value

Try this formula in cell W4: =IF(V3=V4,INDEX(INDIRECT("I"&MATCH(W3,I:I,0)+1&":I26"),MATCH(V4,INDIRECT("R"&MATCH(W3,I:I,0)+1&":R26"),0)),INDEX(\$I\$2:\$I\$26,MATCH(V4,\$R\$2:\$R\$26,0))) The calculation ...READ MORE

How to use an increment an average formula by more than one row in excel?

I believe OFFSET makes it simpler, for ...READ MORE

Return blank cell only if referred cell is blank, but return aging if date is entered

Try this: =IF(ISBLANK(AC2),"",TODAY()-AC2) The TODAY  function automatically refreshes based ...READ MORE