Require Formula

[Johnboy28]

Looking for a formula to match the dates and if "Zone Check" equals "Yes" within that date range, return "Ineligible" and "Eligible" if Zone Check is "No"

1st Answer will be Eligible as All Zone Check were "No"

Date	Zone Check	Date	Zone Check
21/07/2020	eligible	21/07/2020	No
22/07/2020		21/07/2020	No
23/07/2020		21/07/2020	No
24/07/2020		21/07/2020	No
		21/07/2020	No
		21/07/2020	No
		22/07/2020	No
		22/07/2020	No
		22/07/2020	No
		22/07/2020	No
		22/07/2020	Yes
		22/07/2020	No
		22/07/2020	No
		22/07/2020	No
		22/07/2020	No
		22/07/2020	No
		22/07/2020	No
		23/07/2020	No
		23/07/2020	No
		23/07/2020	No
		23/07/2020	No
		23/07/2020	No
		23/07/2020	No
		23/07/2020	No
		23/07/2020	No
		23/07/2020	No
		24/07/2020	Yes
		24/07/2020	No
		24/07/2020	No

 

[navic]

Try the formula in 'B2' (copy down)

=IF(SUMPRODUCT(--($C$2:$C$30=A2),--($D$2:$D$30="Yes"))>0,"Ineligible","Eligible")

 

[Johnboy28]

Thank you very much - that works great.