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

1. ## 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.  Register To Reply

2. ## 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?  Register To Reply

3. ## Re: Index Match Based on One Date Criteria, Table Contains Beginning Date and End Date Originally Posted by FDibbins 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.  Register To Reply

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

Happy to help and thanks for the feedback   Register To Reply