+ Reply to Thread
Results 1 to 6 of 6

Match multiplle criteria between dates

  1. #1
    Registered User
    Join Date
    08-16-2013
    Location
    Miami
    MS-Off Ver
    Excel 2010
    Posts
    11

    Match multiplle criteria between dates

    Hi gurus.. i"ve been trying to match a pruduct/size/brand/date-rage with a table with the same data plus the price. I tried matchin, indexing, vlookup but i have not been able to do it, Any tips?

    Table1

    sale date- Product- Size- Brand
    9/12/2012- Shirts- medium- BrandA
    12/5/2012- Shirts- medium- BrandA
    5/8/2013 - Shirts- medium- BrandA
    9/5/2013 - Shirts- medium- BrandA
    9/12/2012- Shirts- medium- BrandB
    12/5/2012- Shirts- medium- BrandB
    5/8/2013- Shirts- medium- BrandB
    9/5/2013- Shirts- medium- BrandB

    Table 2

    Product- Size- Brand- Star_date- End_date - Price
    Shirt- medium- BrandA- 9/11/2012 12/3/2012 2.55
    Shirt- medium- BrandA- 12/4/2012 3/14/2013 2.45
    Shirt- medium- BrandA- 3/15/2013- 4/29/2013- 2
    Shirt- medium- BrandA- 4/30/2013- 5/6/2013- 2.65
    Shirt- medium- BrandA- 5/7/2013- 5/12/2013- 2.35
    Shirt- medium- BrandA- 5/13/2013- 9/3/2013- 2.25
    Shirt- medium- BrandA- 9/4/2013- 9/3/2099- 1.9
    Shirt- medium- BrandB- 9/11/2012- 12/3/2012- 2.8
    Shirt- medium- BrandB- 12/4/2012- 2/18/2013- 2.7
    Shirt- medium- BrandB- 2/19/2013- 3/14/2013- 2.45
    Shirt- medium- BrandB- 3/15/2013- 4/29/2013- 2.6
    Shirt- medium- BrandB- 4/30/2013- 5/6/2013- 2.77
    Shirt- medium- BrandB- 5/7/2013- 5/12/2013- 2.66
    Shirt- medium- BrandB- 5/13/2013- 5/12/2099- 2.35
    Attached Files Attached Files
    Last edited by colgabusa; 09-05-2013 at 08:30 PM.

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

    Re: Match multiplle criteria between dates

    Hello, try this in F2 7 copy down.

    =AGGREGATE(15,6,Q$2:Q$15/(L$2:L$15=B2)/(M$2:M$15=C2)/(N$2:N$15=D2)/(O$2:O$15<=A2)/(P$2:P$15>=A2),1)

    Or take a look on SUMIFS.
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  3. #3
    Registered User
    Join Date
    08-16-2013
    Location
    Miami
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Match multiplle criteria between dates

    Hi, the Aggregate did not work... it's giving me #NUM!, i'll look into SUMIFS. Tx!

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

    Re: Match multiplle criteria between dates

    My mistake. Sorry about that. i was using 1+ column offset, this should work

    =AGGREGATE(15,6,P$2:P$15/(K$2:K$15=B2)/(L$2:L$15=C2)/(M$2:M$15=D2)/(N$2:N$15<=A2)/(O$2:O$15>=A2),1)

  5. #5
    Registered User
    Join Date
    08-16-2013
    Location
    Miami
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Match multiplle criteria between dates

    it did not work either. Actually i think the first formula was considering the right columns.

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

    Re: Match multiplle criteria between dates

    As per your profile, assuming you are using Excel 2010. AGGREGATE only available excel 2010 or higher versions. See the attached.
    Attached Files Attached Files

+ 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] Highlight Group of Dates if Dates Match List of other Dates
    By martinpgibson in forum Excel General
    Replies: 5
    Last Post: 10-24-2012, 08:14 PM
  2. Replies: 6
    Last Post: 07-09-2012, 11:06 AM
  3. Replies: 6
    Last Post: 01-28-2012, 06:59 PM
  4. Replies: 2
    Last Post: 01-28-2012, 05:26 PM
  5. assign value to multiplle cells
    By bbedson in forum Excel General
    Replies: 1
    Last Post: 08-14-2010, 03:45 PM

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