List Generator with index match within date range

[dhubz]

Hello Eveyone,

I am trying to use an index match to generate a list, but I need to add a criteria for date range. The first sheet is a sample of the data table, on the second we enter the name and week ending date. It generates a list of worker hours based on the table entries. I need the index match formula to only return items within the range.

I need to add the date range portion to this formula

=IFERROR(INDEX(Table1[PF '#], MATCH(0, IF($B$3=Table1[Worker], COUNTIF($D$1:$D1,Table1[PF '#]), ""), 0)),"")

PREFAB DAILY WORK SHEET-SAMPLE FOR CODE.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Column1	PF #	WO #	Order#	JOB NAME	DESCRIPTION	Worker	Date	H0urs	Shipped
2	Chad Pool	PF00013	C00201	043	Site#2	Test Work #2	Chad Pool	10/10/2023	7
3	Jane Doe	PF00012	C00200	002	Site#1	Test Work #1	Jane Doe	10/10/2023	2
4	Chad Pool	PF00022	C01215	051	Site#11	Test Work #11	Chad Pool	10/11/2023	1
5	Jane Doe	PF00022	C01215	051	Site#11	Test Work #11	Jane Doe	10/11/2023	4.5
6	Chad Pool	PF00013	C00201	043	Site#2	Test Work #2	Chad Pool	10/11/2023	5.5
7	John Smith	PF00013	C00201	043	Site#2	Test Work #2	John Smith	10/11/2023	5.5
8	Richard Hertz	PF00013	C00201	043	Site#2	Test Work #2	Richard Hertz	10/10/2023	5
9	Richard Hertz	PF00014	C00202	001	Site#3	Test Work #3	Richard Hertz	10/10/2023	1.5
10	Chad Pool	PF00015	C00203	003	Site#4	Test Work #4	Chad Pool	10/10/2023	7
11	John Smith	PF00015	C00203	003	Site#4	Test Work #4	John Smith	10/12/2023	3.5
12	Chad Pool	PF00016	C00204	072	Site#5	Test Work #5	Chad Pool	10/10/2023	2
13	Jane Doe	PF00016	C00204	072	Site#5	Test Work #5	Jane Doe	10/11/2023	3.5
14	John Smith	PF00017	H00534	029	Site#6	Test Work #6	John Smith	10/10/2023	4
15	Jane Doe	PF00018	H00535	007	Site#7	Test Work #7	Jane Doe	10/10/2023	1
16	John Smith	PF00018	H00535	007	Site#7	Test Work #7	John Smith	10/06/2023	3.5
Daily Work Record

Cell Formulas
Range	Formula
C2:C16	C2	=IFERROR(VLOOKUP([@[PF '#]],Table2[#All],2,FALSE),"")
D2:D16	D2	=IFERROR(VLOOKUP([@[PF '#]],Table2[#All],3,FALSE),"")
E2:E16	E2	=IFERROR(VLOOKUP([@[PF '#]],Table2[#All],4,FALSE),"")
F2:F16	F2	=IFERROR(VLOOKUP([@[PF '#]],Table2[#All],5,FALSE),"")
A2:A16	A2	=[@Worker]

Cells with Data Validation
Cell	Allow	Criteria
G3:G8	List	='Info DB'!$A$1:$A$9
G10:G11	List	='Info DB'!$A$1:$A$9
G13:G14	List	='Info DB'!$A$1:$A$9
G16	List	='Info DB'!$A$1:$A$9

PREFAB DAILY WORK SHEET-SAMPLE FOR CODE.xlsx
	A	B	C	D	E
1		Sunday, October 8, 2023		Job #	Hours
2	Week Ending(mm/dd/yyyy)	Saturday, October 14, 2023		PF00013	5.5
3	Employee	John Smith		PF00015	3.5
4	Employee #	7938		PF00017	4
5	Weekly Total	13		PF00018	0
6					0
7					0
8					0
Employee Weekly Totals

Cell Formulas
Range	Formula
B1	B1	=B2-6
D2:D8	D2	=IFERROR(INDEX(Table1[PF '#], MATCH(0, IF($B$3=Table1[Worker], COUNTIF($D$1:$D1,Table1[PF '#]), ""), 0)),"")
E2:E8	E2	=IFERROR(SUMIFS(Table1[H0urs],Table1[PF '#],'Employee Weekly Totals'!D2,Table1[Worker],$B$3,Table1[Date], ">="&$B$1,Table1[Date], "<="&$B$2),"")
B4	B4	=VLOOKUP(B3,'Info DB'!A:B,2,FALSE)
B5	B5	=SUM(E2:E23)
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B3:C3	List	='Info DB'!$A$1:$A$9

 

[Muhammad_Usman]

To add a date range filter to your existing formula, assuming you want to filter based on the "start date" in cell A1 and the "end date" in cell B1, and the data is in a sheet named "Employee Weekly totals," you can modify the formula like this:

=IFERROR(INDEX('Employee Weekly totals'[PF '#], MATCH(0, IF($B$3='Employee Weekly totals'[Worker], ('Employee Weekly totals'[Date] >= $A$1) * ('Employee Weekly totals'[Date] <= $B$1) * (COUNTIF($D$1:$D1, 'Employee Weekly totals'[PF '#]) = 0), ""), 0)), "")

Here's how this modified formula works:

1. ('Employee Weekly totals'[Date] >= $A$1) * ('Employee Weekly totals'[Date] <= $B$1): This part checks if the date in the "Date" column (adjust the column name as needed) is within the date range defined by cells A1 (start date) and B1 (end date). It returns 1 if the condition is met and 0 if not.
2. The COUNTIF part ((COUNTIF($D$1:$D1, 'Employee Weekly totals'[PF '#]) = 0)) ensures that we only consider values in the "PF #" column that have not already been included in the result, based on your previous logic.

This modified formula will find the first unique "PF #" values from the "Employee Weekly totals" sheet for which the "Worker" matches the value in cell B3 and the "Date" falls within the date range specified in cells A1 and B1.

 

[Peter_SSs]

I need the index match formula to only return items within the range.

See if this is what you want. Note that my dates are in d/m/y format.

dhubz_1.xlsm
	B					G	H
1	PF #					Worker	Date
2	PF00013					Chad Pool	10/10/2023
3	PF00012					Jane Doe	10/10/2023
4	PF00022					Chad Pool	11/10/2023
5	PF00022					Jane Doe	11/10/2023
6	PF00013					Chad Pool	11/10/2023
7	PF00013					John Smith	11/10/2023
8	PF00013					Richard Hertz	10/10/2023
9	PF00014					Richard Hertz	10/10/2023
10	PF00015					Chad Pool	10/10/2023
11	PF00015					John Smith	12/10/2023
12	PF00016					Chad Pool	10/10/2023
13	PF00016					Jane Doe	11/10/2023
14	PF00017					John Smith	10/10/2023
15	PF00018					Jane Doe	10/10/2023
16	PF00018					John Smith	6/10/2023
Daily Work Record

dhubz_1.xlsm
	A	B	C	D
1		8/10/2023		Job #
2	Week Ending(mm/dd/yyyy)	14/10/2023		PF00013
3	Employee	John Smith		PF00015
4	Employee #	7938		PF00017
5	Weekly Total	13
6
7
8
Employee Weekly Totals

Cell Formulas
Range	Formula
B1	B1	=B2-6
D2:D8	D2	=IFERROR(INDEX(Table1[PF '#],AGGREGATE(15,6,(ROW(Table1[PF '#])-ROW(Table1[#Headers]))/((Table1[Worker]=B$3)*(Table1[Date]>=B$1)*(Table1[Date]<=B$2)),ROWS(D$2:D2))),"")