If AND Index Match? formulas

[tutv088]

Hi All I am trying to figure an easy way to determine if a date that the user enters falls within a specific date range. There are 2 criteria that must be met. The final result would be an indicator or simple Yes or No.

User Enters in information in Worksheet 1. 1. Program Name 2. County 3. Date.
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]Program[/TD]
[TD="align: center"]County[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Program Y/N[/TD]
[/TR]
[TR]
[TD]Basketball[/TD]
[TD]Washington[/TD]
[TD]06/01/18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tennis[/TD]
[TD]King[/TD]
[TD]08/06/16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tennis[/TD]
[TD]Davidson[/TD]
[TD]04/13/18[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]









Database of Programs and dates of when they start and End ( Separate workbook with Info)
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]Program[/TD]
[TD="align: center"]County[/TD]
[TD="align: center"]Start[/TD]
[TD="align: center"]End[/TD]
[/TR]
[TR]
[TD="align: center"]Basketball[/TD]
[TD="align: center"]Washington[/TD]
[TD="align: center"]05/01/18[/TD]
[TD="align: center"]12/15/18[/TD]
[/TR]
[TR]
[TD="align: center"]Baseball[/TD]
[TD="align: center"]Washington[/TD]
[TD="align: center"]04/12/18[/TD]
[TD="align: center"]12/14/18[/TD]
[/TR]
[TR]
[TD="align: center"]Baseball[/TD]
[TD="align: center"]Davidson[/TD]
[TD="align: center"]06/08/17[/TD]
[TD="align: center"]06/15/18[/TD]
[/TR]
</tbody>[/TABLE]

I would like the row in the first workbook in Column D3 to say Yes. Thank you guys and appreciate all the help and support.
​

 

[tutv088]

Wow and Thank You so much you guys. The formula worked like a charm. Thank You Ras and JTAKW. Wishing you guys a wonderful day and let me know if theres anything I can ever help you with.

 

[Aladin Akyurek]

In D2 of Sheet1 control+shift+enter and copy down:

=ISNUMBER(MATCH(1,IF(Sheet2!$A$2:$A$4=$A2,IF(Sheet2!$B$2:$B$4=$B2,IF($C2>=Sheet2!$C$2:$C$4,IF($C2<=Sheet2!$D$2:$D$4,1)))),0))+0

A score of 1 means YES, a score of 0 NO.

 

[tutv088]

In D2 of Sheet1 control+shift+enter and copy down:

=ISNUMBER(MATCH(1,IF(Sheet2!$A$2:$A$4=$A2,IF(Sheet2!$B$2:$B$4=$B2,IF($C2>=Sheet2!$C$2:$C$4,IF($C2<=Sheet2!$D$2:$D$4,1)))),0))+0

A score of 1 means YES, a score of 0 NO.

Cool thanks Aladin! So many ways to do it, you guys are so knowledgeable and Excel wizzes

 

[Aladin Akyurek]

Cool thanks Aladin! So many ways to do it, you guys are so knowledgeable and Excel wizzes

You are welcome.

 

[tutv088]

Hi JHAKW, Thank you again for your help. I was running the program with the rest of the data and noticed that it says "Yes"by default even if there was no information entered for the date. It works great by saying Yes or No for the complete data that is entered by the user but when I copy and paste the formula down to the rest of the rows it has a bunch of Yes etc... Is there anyway to blank it out but still leaving the formula in the cell or just copy the formula from the previous entry? Thanks J.

 

[jtakw]

You're quite welcome.

I actually think the formula defaults to a "No", but that's just based on the sample and not your real data, so to leave the Formula cell Blank, when no Date is in Column C of the formula sheet, we'll just add another IF test:

Book1
	A	B	C	D
1	Program	County	Date	Program Y/N
2	Basketball	Washington	6/1/2018	Yes
3	Tennis	King
4	Tennis	King	8/6/2016	No
5	Tennis	Davidson	4/13/2018	No
Sheet142

Cell Formulas
Range	Formula
D2	=IF(C2="","",IF(SUMPRODUCT((Sheet144!A$2:A$4=A2)*(Sheet144!B$2:B$4=B2)*(Sheet144!C$2:C$4<=C2)*(Sheet144!D$2:D$4>=C2)),"Yes","No"))

Book1
	A	B	C	D
1	Program	County	Start	End
2	Basketball	Washington	5/1/2018	12/15/2018
3	Baseball	Washington	4/12/2018	12/14/2018
4	Baseball	Davidson	6/8/2017	6/15/2018
Sheet144

Edit: If you mean "No Dates" entered on the "Database" sheet, let me know, we'll just need to adjust the formula a little.

 

[tutv088]

WOW, thank you so much JTAKW, that worked perfect. Thanks again and have a great day.

 

[jtakw]

You're very welcome.