help matching date and using column related to date with copying row data to new new sheet

[dlmoore99]

Hello smart ones,

I've been trying to get this to work for a while now. I have two sheets: sheet one contains inventory, sheet two will contain a new inventory list based off the the items removed from the inventory on sheet one based of the date the items were removed.

I can make it work when matching one date D1, but as soon as I change the date in I1 to match the other date in E1, I will get #N/A.

so basically I'm trying to move the row data when the quantity of items on specific date are moved from the inventory to create a new list of the items moved for the specific date.

the two (2) helper columns/cells are to help move Row data

If there is a simpler formula to do this, that would be awesome. I'm trying to stay away from VBA and arrays {ctl+shift+enter}

Thank you in advance.

MF Business Report.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Item	Description	On Hand	3/10/25	3/25/25		Helper 1		3/25/25
2	Item 1	Description Item 1	1
3	Item 2	Description Item 2	1
4	Item 3	Description Item 3	13		6		1
5	Item 4	Description Item 4	55	3
6	Item 5	Description Item 5	1
7	Item 6	Description Item 6	5		5		2
8	Item 7	Description Item 7	18	4
9	Item 8	Description Item 8	6		3		3
10	Item 9	Description Item 9	17	4
11	Item 10	Description Item 10	1
12	Item 11	Description Item 11	3
13	Item 12	Description Item 12	4	2
14	Item 13	Description Item 13	1		1		4
15	Item 14	Description Item 14	1		1		5
16	Item 15	Description Item 15	30
17	Item 16	Description Item 16	3		3		6
18	Item 17	Description Item 17	2
19	Item 18	Description Item 18	4		1		7
20	Item 19	Description Item 19	1	1
21	Item 20	Description Item 20	13
22
23
24
25	SHEET 2
26	A	B	C	D	E
27	Item	Description Item	QTY				Helper 2
28	Item 3	Description Item 3	#N/A				7
29	Item 6	Description Item 6	#N/A
30	Item 8	Description Item 8	#N/A
31	Item 13	Description Item 13	#N/A
32	Item 14	Description Item 14	#N/A
33	Item 16	Description Item 16	#N/A
34	Item 18	Description Item 18	#N/A
35
36
37
38
39
Sheet2

Cell Formulas
Range	Formula
G2:G21	G2	=IFERROR(IF(MATCH($I$1,$D$1,0),IF(D2<>"",COUNT($G$1:$G1)+1,"")),IF(MATCH($I$1,$E$1,0),IF(E2<>"",COUNT($G$1:$G1)+1,""),""))
A28:A37	A28	=IF(ROWS($2:2)>$G$28,"",INDEX($A$2:$A$21,MATCH(ROWS('Auction Items'!$179:179),$G$2:$G$21)))
B28:B37	B28	=IF(ROWS($2:2)>$G$28,"",INDEX($B$2:$B$21,MATCH(ROWS('Auction Items'!$179:179),$G$2:$G$21)))
C28:C37	C28	=IF(A28="","",IF(MATCH($I$1,$D$1,0),INDEX($D$2:$D$21,MATCH(ROWS($2:2),$G$2:$G$21)),IF(MATCH($I$1,$E$1,0),INDEX($E$2:$E$21,MATCH(ROWS($2:2),$G$2:$G$21)),"")))
G28	G28	=IF($I$1=$D$1,MAX($G$2:$G$21),IF($I$1=$E$1,MAX($G$2:$G$21),""))
A38:A39	A38	=IF(ROWS($2:17)>$G$28,"",INDEX($A$2:$A$21,MATCH(ROWS('Auction Items'!$179:194),$G$2:$G$21)))
B38:B39	B38	=IF(ROWS($2:17)>$G$28,"",INDEX($B$2:$B$21,MATCH(ROWS('Auction Items'!$179:194),$G$2:$G$21)))

 

[Sam_D_Ben]

Please try this,

=IF(A28="","",IF($I$1=$D$1,INDEX($D$2:$D$21,MATCH(ROWS($2:2),$G$2:$G$21,0)),IF($I$1=$E$1,INDEX($E$2:$E$21,MATCH(ROWS($2:2),$G$2:$G$21,0)),"")))