+ Reply to Thread
Results 1 to 10 of 10

Index/Match on dates

  1. #1
    Forum Contributor mikehk's Avatar
    Join Date
    09-05-2017
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    483

    Index/Match on dates

    =INDEX(Sheet2!C2:C21,MATCH(DATE(Sheet1!B1,Sheet1!A1,Sheet1!A2),Sheet2!A2:A21,0))

    Please help me correct this formula, I get #VALUE error.
    Sample workbook attached herewith.

    Thank you
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Index/Match on dates

    Try this instead...
    =INDEX(Sheet2!C2:C21,MATCH(DATEVALUE(C2&" "&A1&" "&B1),Sheet2!A2:A21,0))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Index/Match on dates

    If you are going to use the DATE function the text October must be coerced into its month number value. If you change formula to
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    it returns USD which is correct ... but there are multiple Mon, 01/Oct/18 dates in 'Sheet2'. Match only finds the first matching matching date. Is that what you want?
    Dave

  4. #4
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Index/Match on dates

    try
    =INDEX(Sheet2!$C$2:$C$21,MATCH((A2&$A$1&$B$1)+0,Sheet2!$A$2:$A$21,0))
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Index/Match on dates

    cool, 3 completely different approaches

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Index/Match on dates

    @ Samba

    That is really cool. I've never seen nor considered that approach.

    It's in my toolbox now.

    Thanks

  7. #7
    Forum Contributor mikehk's Avatar
    Join Date
    09-05-2017
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    483

    Re: Index/Match on dates

    Thank you everyone! all 3 approaches work on the sample file, unfortunately couldn't
    make it work on my actual file. I am very sure I am doing something wrong , and I
    want to find the multiple entries not just the first one.

    Please see the attached file, I hope my query is explained in detail, please let me know
    if need any other info. Sorry, my English is not so good.

    Thank you for your help.
    Attached Files Attached Files

  8. #8
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Index/Match on dates

    Already you have Date in A3 which is formatted to display day only, so use below formula
    =INDEX(CalTime,MATCH(A3,CalDate,0))

  9. #9
    Forum Contributor mikehk's Avatar
    Join Date
    09-05-2017
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    483

    Re: Index/Match on dates

    Thank you so much...this works, but I would like to get all data under that date

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Index/Match on dates

    This proposal employs structured references.
    The formula for A4:A17 is: =IFERROR(INDEX(Table1[Time],AGGREGATE(15,6,(ROW(Table1[Date])-1)/(Table1[[Date]:[Date]]=$A$3),ROW(1:1))),"")
    The formulas for the other columns are similar.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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] Excel - INDEX MATCH BETWEEN DATES
    By ironfelix717 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-18-2017, 03:24 PM
  2. Index/Match for Dates
    By dnctd in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-29-2017, 05:19 AM
  3. 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
  4. [SOLVED] Using INDEX(MATCH()) with dates
    By SHUTTEHFACE in forum Excel General
    Replies: 14
    Last Post: 06-21-2016, 02:38 AM
  5. INDEX MATCH with multiple same dates
    By mcayea in forum Excel Formulas & Functions
    Replies: 23
    Last Post: 07-08-2013, 10:58 AM
  6. Match & Index Between Dates
    By jennyaccord in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-14-2011, 01:07 PM
  7. INDEX MATCH with dates in a ROW
    By thart21 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 06-02-2011, 12:15 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