Excel Formula with Nested IF LEFT AND Functions

Question

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. 

Answer 1

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.