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.
​

 

[RasGhul]

Hi Tut,

This post looks familiar...

	A	B	C	D
Program	County	Date	Program Y/N
Basketball	Washington
Tennis	King
Tennis	Davidson
Program	County	Start	End
Basketball	Washington
Baseball	Washington
Baseball	Davidson

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]01-06-18[/TD]
[TD="align: center"]Y[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]06-08-16[/TD]
[TD="align: center"]N[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]13-04-18[/TD]
[TD="align: center"]Y[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]01-05-18[/TD]
[TD="align: right"]15-12-18[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]12-04-18[/TD]
[TD="align: right"]14-12-18[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]08-06-17[/TD]
[TD="align: right"]15-06-18[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]=IF(AND(C2>C7,C2<d7< font="">),"Y","N"</d7<>)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D3[/TH]
[TD="align: left"]=IF(AND(C3>C8,C3<d8< font="">),"Y","N"</d8<>)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D4[/TH]
[TD="align: left"]=IF(AND(C4>C9,C4<d9< font="">),"Y","N"</d9<>)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

OP Also needs the "Program" and "County" included in the criterion as well as the dates, and the 2 Tables are in Different Workbooks...

Formula in Post #2 seems incomplete and does not address all requirements (might have gotten cut off).

 

[tutv088]

Hi Ras and thank you for helping me with this problem. I suppose I wasn't clear in my explanation and was hoping for a formula where when the user enters the Program, County and Date that it scans down the column of A:A, B:B doing a match then it will determine if the date the user entered is between the Start and End date. I know that gets complicated and thats why I am reaching out. The Program Country Start and End date is located in a different worksheet database. Hope that makes sense and thanks again buddy.

 

[tutv088]

Hi Ras, so for instance. I will describe what the formula should be doing. User Enters Program, County and Date. The formula in D2 should say something like take A2&B2 then scan in worksheet 2 column A:A&B:B and look over to the right to see if the date that the user entered is between the ranges of Start and End for that particular combo of Program and County.

 

[tutv088]

Thanks Jtakw

 

[RasGhul]

Yeah formula got cut off, sorry I rushed it a bit and misread the goal. I'll look again.

 

[tutv088]

Thank You Ras, I appreciate it. Take your time no worries

 

[jtakw]

In your OP, you indicated the two Tables are in Different Workbooks, but then in Post #4 and #5 , you indicated they're just in different Worksheets (that would mean same Workbook), so I'm assuming it's just different Worksheets within the same Workbook:

Try and see if this does what you need:

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

Cell Formulas
Range	Formula
D2	=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

Sheet142 D2 formula copied down.

Change Sheet144 (Database sheet name) to match yours, change/adjust Cell reference/range as needed.

 

[RasGhul]

Snap

=IF(SUMPRODUCT((C2>=$C$7:$C$9)*(C2<=$D$7:$D$9)*($A$7:$A$9&$B$7:$B$9=A2&B2)),"Yes","No")