+ Reply to Thread
Results 1 to 7 of 7

Nested IF statement to return a value based on multiple criteria

  1. #1
    Registered User
    Join Date
    04-08-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    30

    Nested IF statement to return a value based on multiple criteria

    Hello Excel Gurus,

    I am trying to return a value based on multiple criteria using a nested IF statement. Basically, I have a table that contains costs for a direct mail campaign, and I am trying to return the cost of the program based on the values that can be selected in two separate cells which both have drop-down lists. There are 20 different possible values that can be returned based on the possible combination of selections in those two separate cells.

    If you look at the attached spreadsheet, the cell that I am trying to return the costs to is cell B4 (Total Program Costs). The possible values that can be returned are all in column G.

    The statement should return values based on two criteria:

    1) It should compare the value of B2 with the values in Row F
    2) It should also compare the value of B3 and compare with the values in Row A.

    It should then return the value based on the way the table is set up.

    So, if a user selects "Long Mailer" in cell B3, and 100,000 in B2, then cell B4 should return a value of $43,300.

    If a user selects "Jumbo Postcard" in cell B3, and 25,000 in B2, then cell B4 should return a value of $11,500.

    I attempted to solve the problem using a really long nested IF statement using the AND command as well. I got an error message in Excel telling me that I had exceeded the max amount of levels in a nested statement. I am assuming there is a more elegant way to do this, but I cannot figure it out.

    Help!

    Thanks.
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Nested IF statement to return a value based on multiple criteria

    I think that you want to use a lookup function and I think that it can best be shown in the video (very long) referenced below.

    http://www.youtube.com/user/ExcelIsF.../5/bMWv0AN-hSs

    Alan
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Nested IF statement to return a value based on multiple criteria

    Hi,

    Give this a try.

    Cheers,
    Attached Files Attached Files
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  4. #4
    Registered User
    Join Date
    04-08-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Nested IF statement to return a value based on multiple criteria

    This worked perfectly. If it's too much trouble, do you think you can explain what you did?

    Thanks so much!

    Quote Originally Posted by ConneXionLost View Post
    Hi,

    Give this a try.

    Cheers,

  5. #5
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Nested IF statement to return a value based on multiple criteria

    Hi again, sorry for the delay.

    Two things were changed in your workbook.

    First, I changed the items in your "Mailer_Type" list to ensure an exact match was possible with the items in the 'Direct Mail'!A19:A45 range.

    Second I added the formula:

    Please Login or Register  to view this content.
    to the 'Direct Mail'!B4 cell.

    This formula is thoroughly explained in this SUMPRODUCT article.

    Cheers,

  6. #6
    Registered User
    Join Date
    07-27-2011
    Location
    Cape Town, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Nested IF statement to return a value based on multiple criteria

    Hi All - help please!
    In the attached spreadsheet, if any of the assets rows 7 to 29 have a number in column K, I would like that information in the relevant row to show in rows 71 to 80 in order.

    any recommendations?
    Attached Files Attached Files

  7. #7
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Nested IF statement to return a value based on multiple criteria

    Hi azb0105,

    With regard to Forum Rule #2, I recommend you start your own post.

    Cheers,

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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