+ Reply to Thread
Results 1 to 8 of 8

Nested IF Formula

  1. #1
    Registered User
    Join Date
    12-09-2014
    Location
    Wales
    MS-Off Ver
    2016
    Posts
    80

    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
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: Nested IF Formula

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


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: Nested IF Formula

    Will this do?

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

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    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. #5
    Registered User
    Join Date
    12-09-2014
    Location
    Wales
    MS-Off Ver
    2016
    Posts
    80

    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. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    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. #7
    Registered User
    Join Date
    12-09-2014
    Location
    Wales
    MS-Off Ver
    2016
    Posts
    80

    Re: Nested IF Formula

    It works perfectly, thank you !

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: Nested IF Formula

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Value of cell formula not working in nested IF formula.
    By dschierman in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-01-2019, 04:59 PM
  2. [SOLVED] Nested IF and nested Concatenation formula
    By VisionSmart in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-09-2018, 08:00 AM
  3. Help with a nested, nested, nested formula
    By duanrd2 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-09-2017, 04:43 PM
  4. Nested If formula
    By KODIAKITA in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-15-2015, 01:48 PM
  5. Replies: 2
    Last Post: 08-24-2014, 04:56 AM
  6. Replies: 2
    Last Post: 10-08-2009, 09:51 PM
  7. help on a nested countif/nested if formula
    By vickiemc in forum Excel General
    Replies: 2
    Last Post: 08-13-2008, 08:29 AM

Tags for this Thread

Bookmarks

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