+ Reply to Thread
Results 1 to 15 of 15

A complicated Look UP table with multiple conditions

  1. #1
    Forum Contributor
    Join Date
    06-26-2012
    Location
    UAE
    MS-Off Ver
    Excel 2003
    Posts
    274

    A complicated Look UP table with multiple conditions

    Hello Dear Excel Mates,

    I have a table (attached) where i need to to auto populate Net Sales values with multiple conditions. This is tearing my brain down. I hope the excel mates in here can help me solve this.

    much appreciated

    Regards,
    Excel Dumbo
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    07-12-2012
    Location
    Perth, Australia
    MS-Off Ver
    Office 2007
    Posts
    1

    Re: A complicated Look UP table with multiple conditions

    I've put an attempt up. My first post here, but i belive it address your issue. Main problem is that if a situation where a Brand Text Is the same as Product Text it will count it twice. you can use the same formulea and an if statement to return blanks rather than zeros if that is your preference.

    I've also had to add a line above the month which is in the same format as your date field. If this is a problem for formatting then just change the font color to white so you can't see it.
    Attached Files Attached Files

  3. #3
    Valued Forum Contributor gjlindn's Avatar
    Join Date
    08-01-2011
    Location
    Dodgeville, WI
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    369

    Re: A complicated Look UP table with multiple conditions

    Hi. I think this will do what you want, but I did add a row of years to the file. Here's the formula (same formula is used in all cells)
    Please Login or Register  to view this content.
    Note that if you expand the lookup range you'll need to replace all 29s in the formula with the last row of your data.

    Good luck!
    Attached Files Attached Files
    Last edited by gjlindn; 07-12-2012 at 11:36 PM.
    -Greg If this is helpful, pls click Star icon in lower left corner

  4. #4
    Valued Forum Contributor gjlindn's Avatar
    Join Date
    08-01-2011
    Location
    Dodgeville, WI
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    369

    Re: A complicated Look UP table with multiple conditions

    Quote Originally Posted by DamoMK View Post
    I've put an attempt up. My first post here, but i belive it address your issue. Main problem is that if a situation where a Brand Text Is the same as Product Text it will count it twice. you can use the same formulea and an if statement to return blanks rather than zeros if that is your preference.

    I've also had to add a line above the month which is in the same format as your date field. If this is a problem for formatting then just change the font color to white so you can't see it.
    Hi DamoMK...nice solution! Same approach I used, but something I found is that if you have a sum where both criteria are met, you'll double the value used (2 instead of 1) and end up doubling the result. Example if the sumproduct resulted in (TRUE*TRUE*(TRUE+TRUE)) the value would be 2 rather than 1.

  5. #5
    Forum Contributor
    Join Date
    06-26-2012
    Location
    UAE
    MS-Off Ver
    Excel 2003
    Posts
    274

    Re: A complicated Look UP table with multiple conditions

    Hi THanks for your help. I tired the formula. It's woorking for the first variable for the first month, then it's going wrong.

    I have attached the file. Please kindly advise
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor gjlindn's Avatar
    Join Date
    08-01-2011
    Location
    Dodgeville, WI
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    369

    Re: A complicated Look UP table with multiple conditions

    You could do one of two things:
    Change the formula to (E4 formula then copy to rest of range)
    Please Login or Register  to view this content.
    Or use the existing formula, but make sure there are no blanks in your lookup data range in colums B and C (or add some value to columns C and D of the results tab that would never be found in columns B and C of the lookup data tab)

    Thanks!

  7. #7
    Forum Contributor
    Join Date
    06-26-2012
    Location
    UAE
    MS-Off Ver
    Excel 2003
    Posts
    274

    Re: A complicated Look UP table with multiple conditions

    THank you very much for this. THe formula works wonderfully
    The only thing that bothers me is, the file size keeps growing and it takes lot of time to auto populate values (percentage of completion going slow at the bottom left tab)

    Is there a way where we can easily do this ? Like a Macro or something ?

  8. #8
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: A complicated Look UP table with multiple conditions

    This doesn't solve all your problems but it should address most and do it faster.
    Firstly I made the "dates" in E2:J2 actual first of month dates formatted as mmm.
    Secondly, I also made the "dates" Result!A:A actual first of month dates formatted as "m.yyyy".
    It also relies on your Result sheet being sorted by column A (as it is now in the sample).

    Then in E4 (copied over and down) this beastly non-array SUMIF() formula:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    It will not pick up your double entry search values in C8. I would suggest splitting those up, otherwise the formula will get ridiculous-er.

    Here is your file with the formulas in place:
    Attached Files Attached Files
    Last edited by Cutter; 07-15-2012 at 08:26 PM.

  9. #9
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: A complicated Look UP table with multiple conditions

    here's another option with a concatenated helper,depends on what the whole thing looks like really but i'm with cutter split reusable/disposable into two
    Attached Files Attached Files
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  10. #10
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: A complicated Look UP table with multiple conditions

    With your original sample file, try this in E4, then copy down & across.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  11. #11
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: A complicated Look UP table with multiple conditions

    Haseeb

    OP apparently has 25,000 rows which caused previous SUMPRODUCT() suggestions to bog down.

  12. #12
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: A complicated Look UP table with multiple conditions

    @Hasseb A do you think that will speed things up? as in post #7

  13. #13
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: A complicated Look UP table with multiple conditions

    I am sorry, my mistake. I didn't go through all posts.

  14. #14
    Forum Contributor
    Join Date
    06-26-2012
    Location
    UAE
    MS-Off Ver
    Excel 2003
    Posts
    274

    Re: A complicated Look UP table with multiple conditions

    Thanks Guys . You have been an awesome help.

  15. #15
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: A complicated Look UP table with multiple conditions

    You're welcome. Thanks for the 'star tap'.

+ 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