Excel Formula with Nested IF LEFT AND Functions

0 votes

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 in Others by Kithuzzz
• 20,660 points
35 views

1 answer to this question.

0 votes

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.

answered Nov 21 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
0 answers

Combining MID and LEFT Functions in Excel 2019

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

Nov 27 in Others by Kithuzzz
• 20,660 points
16 views
0 votes
0 answers

MS Excel - SumProduct formula with Loop

1 I have 4 arrays of data where ...READ MORE

Feb 18 in Others by Edureka
• 13,640 points
81 views
0 votes
1 answer

Excel VBA if file closed, then open and paste, else just paste data

Slightly re-worked to add full workbook/sheet qualifiers ...READ MORE

answered Sep 21 in Others by narikkadan
• 37,660 points
86 views
0 votes
1 answer

IF function in combination with an Round function Excel

I'm not sure if it is a ...READ MORE

answered Sep 25 in Others by narikkadan
• 37,660 points
59 views
0 votes
1 answer

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

answered Oct 8 in Others by narikkadan
• 37,660 points
57 views
0 votes
1 answer

How Can I Round Prices to the nearest 0.95 with an Excel Formula?

Try this: =IF(OR(A3-FLOOR(A3,1)>0.95,A3=CEILING(A3,1)),CEILING ...READ MORE

answered Oct 9 in Others by narikkadan
• 37,660 points
50 views
0 votes
1 answer

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

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

answered Nov 24 in Others by narikkadan
• 37,660 points
27 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
142 views
0 votes
1 answer

Excel - IF Formula with a FIND

What about using mid() to see if ...READ MORE

answered Sep 27 in Others by narikkadan
• 37,660 points
56 views
0 votes
1 answer

IF formula to compare a date with current date and return result

You can enter the following formula in ...READ MORE

answered Sep 27 in Others by narikkadan
• 37,660 points
60 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP