+ Reply to Thread
Results 1 to 4 of 4

Index Match Based on One Date Criteria, Table Contains Beginning Date and End Date

  1. #1
    Registered User
    Join Date
    12-01-2012
    Location
    Charleston, SC
    MS-Off Ver
    Excel 2010
    Posts
    23

    Index Match Based on One Date Criteria, Table Contains Beginning Date and End Date

    My first post here for assistance.

    I have a table in cells A1:C4 as follows:
    A B C D E
    1 Name ArriveDate DepartDate Query Date
    2 Smith 10/1/14 10/8/14 10/25/14
    3 Black 10/22/14 10/29/14 Name
    4 Jones 11/14/14 11/21/14

    I've assigned Range Names to each column in the table: Name, ArriveDate and DepartDate

    My criteria is the date in cell E2: 10/25/14. I want a formula that would place a name in cell E4 where the ArriveDate is <= 10/25/14 and the DepartDate is >= 10/25/14.

    I tried this formula in cell e4:
    =(index(Name,Match(1,(value($e$2)>=ArriveDate)*(value($e$2)<=DepartDate),0))

    ... but it didn't work. (I hit CONTROL + SHIFT + ENTER and the formula went into the cell, but no brackets).

    I'm assuming that I cannot use the >= and <=, but I do not know how to accomplish this another way - the data in the table cannot be sorted in order to accommodate a match type.

    Any assistance you might provide would be greatly appreciated. Thank you in advance for your help.
    Last edited by jcox1953; 02-09-2014 at 10:57 AM. Reason: Issue Solved

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,996

    Re: Index Match Based on One Date Criteria, Table Contains Beginning Date and End Date

    Using your formula (but removing an extra () )...
    =INDEX(Name,MATCH(1,(VALUE($E$2)>=ArriveDate)*(VALUE($E$2)<=DepartDate),0))
    also this...
    =INDEX(Name,MATCH(1,(ArriveDate<=E2)*(DepartDate>=E2),0))
    ARRAY entered CTRL SHIFT ENTER, gives me an answer of black?
    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
    Registered User
    Join Date
    12-01-2012
    Location
    Charleston, SC
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Index Match Based on One Date Criteria, Table Contains Beginning Date and End Date

    Quote Originally Posted by FDibbins View Post
    Using your formula (but removing an extra () )...
    =INDEX(Name,MATCH(1,(VALUE($E$2)>=ArriveDate)*(VALUE($E$2)<=DepartDate),0))
    also this...
    =INDEX(Name,MATCH(1,(ArriveDate<=E2)*(DepartDate>=E2),0))
    ARRAY entered CTRL SHIFT ENTER, gives me an answer of black?

    Thank you very much. I got that to work in my worksheet. Much appreciated.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,996

    Re: Index Match Based on One Date Criteria, Table Contains Beginning Date and End Date

    Happy to help and thanks for the feedback

+ 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 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
  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 multiple criteria based on earliest date
    By dchubbock in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-28-2013, 07:18 PM
  4. [SOLVED] INDEX MATCH based on 5 criteria, including a date range.
    By Folshot in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-05-2013, 09:35 PM
  5. Index / match 2 criteria row and column containing date - please help!!
    By willgt in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-22-2013, 08:03 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