+ Reply to Thread
Results 1 to 2 of 2

INDEX date that MATCH on date range, MATCH code(string)

  1. #1
    Forum Contributor
    Join Date
    06-18-2012
    Location
    Kuching, SARAWAK
    MS-Off Ver
    Excel 2010
    Posts
    116

    INDEX date that MATCH on date range, MATCH code(string)

    Hi Excel Experts,

    I have a table consists of the followings:-

    Col A Col B
    2/2/2014 PH
    5/2/2014 PH
    21/2/2014 PH
    23/2/2014 PH
    25/2/2014 CL
    3/3/2014 PH
    6/3/2014 EV
    9/3/2014 PH
    12/3/2014 PH
    20/3/2014 EV
    22/3/2014 EV
    25/3/2014 PH
    28/3/2014 CL
    1/1/2015 EV
    5/1/2015 PH
    11/1/2015 PH
    12/1/2015 PH
    18/1/2015 PH
    19/1/2015 PH
    20/1/2015 PH
    23/1/2015 PH
    24/1/2015 EV
    25/1/2015 CL
    26/1/2015 PH

    Please take note that dates in Col A are values from Index Match from another worksheet).
    I want to get the date which match the following criterias which will be manual entries by the user.

    E.g.
    C1 : 2014 (data validation list)
    C2 : February (data validation list)
    C3 : CL (returned value from index match from another worksheet)

    So far, I fail to get the date because I don't know how to set the MATCH formula for the Month name in C2 to match the month in the range of dates in Col A. Below is what I got so far (by logic thinking):-

    {=INDEX($A$11:$A$80,MATCH(1,(MONTH($A$11:$A$36)=$C$2)*("CL"=$B$11:$B$80),0))} (Note: {} is from CTRL+SHIFT+ENTER)

    I'm trying to extract the month in the range of dates that meets the criteria mentioned in C2, but format for both are different. I tried using DATEVALUE or TEXT($A$11:$A$80,"mmmm") still error.

    Appreciate if someone can help promptly.

    Thank you in advance.

  2. #2
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    2,235

    Re: INDEX date that MATCH on date range, MATCH code(string)

    Maybe you have the sample workbook to make more clear the real problem is? Try to upload it at the forum

    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. [SOLVED] index match with sumif date range
    By namluke in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-09-2017, 09:45 AM
  2. [SOLVED] Index Match Based on date range and name criteria
    By darkhangelsk in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-02-2014, 11:33 PM
  3. [SOLVED] Index Match with date range criteria
    By NS4Excel in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 10-24-2013, 04:58 PM
  4. Index match, within a certain date range
    By Spreadsheetdaunting in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-08-2013, 01:02 AM
  5. Replies: 5
    Last Post: 01-06-2012, 08:00 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