+ Reply to Thread
Results 1 to 4 of 4

Extracting information based on mutliple criteria and date range

  1. #1
    Registered User
    Join Date
    07-25-2016
    Location
    Bowling Green, KY
    MS-Off Ver
    2013
    Posts
    3

    Extracting information based on mutliple criteria and date range

    I've been working on this equation for a few days now to extract information based on matching criteria within a date range. A couple of formulas I've tried are index/match and sumifs but cant get either to work. So here is what I have, in columns B and C are my date ranges, column D is the product and E is the TPH. Then in column H I have my product I'm trying to match based on the date ranges by month in Row 2 going across. What I'd like to do is publish the TPH that corresponds with the product and the month it's located in I2:T10 (highlighted section).
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,193

    Re: Extracting information based on mutliple criteria and date range

    This is based on FROM date:

    in I3

    =IFERROR(INDEX($E$2:$E$41,MATCH(1,($H3=$D$2:$D$41)*(I$2=$B$2:$B$41),0)),"")

    Enter with Ctrl+Shift+Enter

    Copy down and across

  3. #3
    Registered User
    Join Date
    07-25-2016
    Location
    Bowling Green, KY
    MS-Off Ver
    2013
    Posts
    3

    Re: Extracting information based on mutliple criteria and date range

    Thanks for the quick response but when I enter the formula it only displays the TPHs that begin with the From date. Are there any other ways that it can see the date in Row 2 and distinguish if it falls between the date range in columns B and C?

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,193

    Re: Extracting information based on mutliple criteria and date range

    Try

    =IFERROR(INDEX($E$2:$E$41,MATCH(1,($H3=$D$2:$D$41)*(I$2>=$B$2:$B$41)*(I$2<=$C$2:$C$41),0)),"")

    Enter with Ctrl+Shift+Enter (as before)

    This check for dates >= to B dates and <= to C Dates

+ 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] Split Excel Sheet Data into mutliple files based on two criteria
    By saleembasha in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-22-2016, 12:14 PM
  2. [SOLVED] Extracting Data from a Date Range and Criteria
    By balloonfiasco in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-10-2015, 03:29 PM
  3. Extracting data from a range that changes daily based on a cells criteria
    By JohnnyBoyxxx in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-02-2014, 02:35 AM
  4. Extracting number of months from date range based on specific criterea
    By trianglehead in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-24-2013, 07:27 PM
  5. Replies: 5
    Last Post: 02-10-2013, 06:54 PM
  6. Replies: 1
    Last Post: 05-29-2012, 04:53 PM
  7. To automate a list based of mutliple column criteria
    By money n da sank in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-24-2008, 11:16 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