+ 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
    52

    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
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,514

    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!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,514

    Re: Nested IF Formula

    Will this do?

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

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,514

    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
    52

    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
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,514

    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
    52

    Re: Nested IF Formula

    It works perfectly, thank you !

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,514

    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