+ Reply to Thread
Results 1 to 2 of 2

Index Match within specified date range

  1. #1
    Registered User
    Join Date
    02-21-2019
    Location
    Canada
    MS-Off Ver
    microsoft excel for office 365 mso
    Posts
    6

    Index Match within specified date range

    Hello,

    I have a spreadsheet with 15-minute interval data for an entire year. I would like to pick out the maximum value (demand) from each month, and return the date and time which the max demand occurred.

    To get the max demand for February, I have:

    MAX(IF(MONTH(Table1[Date and Time])="2", Table1[[Demand ]:[Demand ]]))

    Now to find the date at which this max occurred is where I am struggling. My attempt is:

    INDEX(Table1, IF(MONTH(Table1[Date and Time])=2, MATCH(MAX_VALUE,Table1[[Demand ]],0),""),1)

    Any suggestions? Sorry if I missed anything in the rules, this is my first post!
    Last edited by julesjanker; 02-21-2019 at 03:28 PM. Reason: Edited to replace cell reference with an explanation of what the cell is.

  2. #2
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Index Match within specified date range

    Without a sample, my best assumption would be:
    INDEX(Table1, MATCH(MAX(IF(MONTH(Table1[Date and Time])="2", Table1[[Demand ]:[Demand ]])) ,Table1[[Demand ]],0),""))
    Array entered.
    Click the * to say thanks.

+ 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. index match with date range help!
    By jzucco in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-13-2017, 10:05 AM
  2. Index Match to find match date to a range of dates
    By downcrusher in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-27-2017, 06:05 PM
  3. Replies: 10
    Last Post: 01-15-2017, 08:02 AM
  4. [SOLVED] Index/Match Formula for Date Range
    By Brawnystaff in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-18-2015, 02:08 AM
  5. [SOLVED] INDEX date that MATCH on date range, MATCH code(string)
    By Dahlia in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-23-2014, 03:33 AM
  6. [SOLVED] Index Match with date range criteria
    By NS4Excel in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 10-24-2013, 04:58 PM
  7. Index match, within a certain date range
    By Spreadsheetdaunting in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-08-2013, 01:02 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