+ Reply to Thread
Results 1 to 7 of 7

Simpler way than 10+ nested IF statements?

  1. #1
    Registered User
    Join Date
    09-14-2015
    Location
    texas
    MS-Off Ver
    2010
    Posts
    2

    Question Simpler way than 10+ nested IF statements?

    I've been stuck on this for hours, any excel wizards know of a way to simplify this problem?

    I have a spreadsheet that calculates the gas rate based on what area it is located in (there are 24 areas). Instead of making multiple nested if statements with vlookups embedded, is there any easier way to do this? Perhaps VBA or an array function?

    See attached excel spreadsheet. Gas rate is calculated in column E3.

    Forecasting Master Excel Help.xlsx

    Thank you

  2. #2
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,379

    Re: Simpler way than 10+ nested IF statements?

    There is


    Kind regards
    Leo

  3. #3
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,379

    Re: Simpler way than 10+ nested IF statements?

    en the file


    little error
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    07-24-2009
    Location
    Valrico, FL USA
    MS-Off Ver
    Excel 2016
    Posts
    358

    Re: Simpler way than 10+ nested IF statements?

    Try the attached file on sheet2.
    I changed the lookup area and the formula.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    09-14-2015
    Location
    texas
    MS-Off Ver
    2010
    Posts
    2

    Re: Simpler way than 10+ nested IF statements?

    Quote Originally Posted by ncmay View Post
    Try the attached file on sheet2.
    I changed the lookup area and the formula.
    Wow this is great thank you! What would you do if the lookup value columns are all irregular in size? See tab 1 in this file attached below.

    Forecasting Master Excel Help2.xlsx

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,801

    Re: Simpler way than 10+ nested IF statements?

    LeoTaxi and ncmay, Sorry for off-topic interjection:

    Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

    I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

    Thanks again for all your hard work here!
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,801

    Re: Simpler way than 10+ nested IF statements?

    jobinv, try this, cvopied down...
    =INDEX($H$3:$AA$14,MATCH($D2,OFFSET($H$1,2,MATCH("Area "&$A2,$H$1:$AA$1,0)-1,50,1)),MATCH("Area "&$A2,$H$1:$AA$1,0)+1)
    (I think your last answer - 5900 - is wrong. That value is against 0.62, not 0.75)

+ 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. [SOLVED] Simpler way than 11+ If Statements?
    By ker9 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 08-28-2015, 04:24 PM
  2. Replies: 0
    Last Post: 09-29-2013, 04:50 AM
  3. Replies: 2
    Last Post: 05-22-2013, 05:39 PM
  4. Help make this simpler (if statements)
    By harmbasi in forum Excel General
    Replies: 3
    Last Post: 04-13-2012, 02:58 PM
  5. A simpler way to do nested vlookups?
    By bsoper in forum Excel General
    Replies: 4
    Last Post: 01-06-2011, 03:43 PM
  6. Looking for a simpler formula instead of using may nested "IF"s
    By Ogbuehi in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-19-2009, 11:15 AM
  7. Replies: 12
    Last Post: 05-15-2009, 08:38 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