Lookup based on date range

[ryansm05]

Hi,

I'm trying to find a formula that will help with the below conundrum (sorry for not using the XL2BB, but I don't have permission to download on my work laptop).

Basically, The criteria can be found in cell B7, B8 and B9 and the correct result of 3 has been returned in cell B10. What this is doing is:

1) Looking for a match along row 1 which is between the dates of 10/09/2022 and 18/09/2022
2) Looing for a match between B2:B4 for Team 3
3) Returning any cell that has text between cells C2:K4 (for what it's worth the cell with always be in the format XYZ(A) or ABC(H))

	A	B	C	D	E	F	G	H	I	J	K
1			10/09/2022​	11/09/2022​	12/09/2022​	16/09/2022​	17/09/2022​	18/09/2022​	01/10/2022​	02/10/2022​	03/10/2022​
2		Team 1				WHU(H)	SOU(A)				MUN(H)
3		Team 2		NOT(A)			NEW(H)				WHU(A)
4		Team 3	NOT(H)			MUN(A)		AVL(H)			CRY(A)
5
6
7	Team	Team 3
8	Start Date	10/09/2022
9	End Date	18/10/2022
10	Result	3

If anyone can help it'd be much appreciated.

Thanks
Ryan

 

[Fluff]

Returning any cell that has text between cells C2:K4

How does that equate to a result of 3? Or are you just trying to count how many cells have text?

 

[ryansm05]

For avoidance of doubt, the data in cell B9 should be 18/09/2022 - which is why only 3 values are returned.

Thanks
Ryan

 

[ryansm05]

How does that equate to a result of 3? Or are you just trying to count how many cells have text?

The answer will be 3 because it's based on the criteria of Team 3 & data ranges as per the above. I basically see this as a fancy index/match, but I was unsure of how to build in a match criteria between dates.

 

[Fluff]

But why is it 3? Is it because there are 3 cells with a value?

 

[ryansm05]

But why is it 3? Is it because there are 3 cells with a value?

Yes exactly. Three cells are populated with text. It's basically fixtures that a football team will play in a certain date period. For example, if the date criteria was extended to 03/10/2022 the answer would be 4 because of the CRY(A) fixture.

Thanks
Ryan

 

[Fluff]

Ok, how about

=SUMPRODUCT((B2:B4=B7)*(C1:K1>=B8)*(C1:K1<=B9)*(C2:K4<>""))

 

[ryansm05]

Ok, how about

=SUMPRODUCT((B2:B4=B7)*(C1:K1>=B8)*(C1:K1<=B9)*(C2:K4<>""))

Worked perfectly! Thanks Fluff

 

[Fluff]

Glad to help & thanks for the feedback.