Nested IF Formula

1. Nested IF Formula

Hi,

I need some help on I believe a nested IF formula.

In the attached workboook the formula would start in cell D6 and go down to cell D13.
In an ideal world, column L would be completely filled in with customer names and I would just to do =L6 and drag down.

However, if an order number from column C starts with a 7, then the customer name does not appear in column L.

There is two rules for an order number starting with a 7.
Rule 1 is if the grade is COFFEEESS 138 then the customer is DPK.
Rule 2 is if the grade is not COFFEEESS 138 then the customer is LGT.

So I think I need 3 separate calculations but in the same same formula. The formula would firstly check if there is a customer name in column L, if it is then it takes that customer name. If there is no customer name then I would need to apply the 2 rules above.

Any help on this would be greatly appreciated.

Aarron

2. Re: Nested IF Formula

Are you still using Excel 2007? If not, please update your profile.

3. Re: Nested IF Formula

Will this do?

=IF(L6="",M6,L6)

4. Re: Nested IF Formula

Or this, if you need it to be a bit smarter and more dynamic:

=IF(VLOOKUP(C6,\$K\$6:\$M\$13,2,0)=0,VLOOKUP(C6,\$K\$6:\$M\$13,3,0),VLOOKUP(C6,\$K\$6:\$M\$13,2,0))

5. Re: Nested IF Formula

Hi, thank you for pointing out that my profile was outdated; its changed now

The above formulas work for the first part of the calculation; however if the order begins with a 7 the customer name will either be DPK or LGT depending on the grade name.
I am wondering if I should have another tab with these two customers on that could be used to VLOOKUP with. The only problem is I have no idea how to put that in your formula.

6. Re: Nested IF Formula

No need. This will do it:

=IF(VLOOKUP(C6,\$K\$6:\$M\$13,2,0)=0,IF(VLOOKUP(C6,\$K\$6:\$M\$13,3,0)="COFFEEESS 138","DPK","LGT"),VLOOKUP(C6,\$K\$6:\$M\$13,2,0))

7. Re: Nested IF Formula

It works perfectly, thank you !

8. Re: Nested IF Formula

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1