To list down data from another sheet using IFERROR formula with multiple criteria

[Kenor]

Hi All,

This entry actually has similarities to my previous entry.

Only this time, I tried to use the same formula to get data from multiple criteria.

I have amended the formula, but not sure it is correct or not.
(Having two criteria is fixed but the other two criteria are variable)

Need anyone's help to verify.

I use this formula to get data from another sheet:

=IFERROR(INDEX(STORAGE_DATA!$H:$H,AGGREGATE(15,6,ROW(STORAGE_DATA!$H$3:$H$200)/($I$1=TRIM(STORAGE_DATA!$D$3:$D$200))/($K$1=STORAGE_DATA!$G$3:$G$200)/($H$3=STORAGE_DATA!$E$3:$E$200)/($L$3=STORAGE_DATA!$F$3:$F$200),ROWS($I$3:$I3))),"")

Can refer to the below data table,

FG Database Bundle.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	SUMMARY → A6082							INVENTORY →	A6082		50
2	Ø	IN (pcs)	OUT (pcs)	TOTAL STOCK	Remark			BUNDLE NO	IN (pcs)	OUT (pcs)	TOTAL STOCK	LOT NO
3								22-602	6			03322V2
4		0	0	0				22-602				05322V1
5		0	0	0				22-603				05322V1
6		0	0	0				22-604				05322V1
7		0	0	0				22-605				05322V1
8								22-606				05322V1
9	TOTAL =	0	0	0				22-607				05322V1
10								22-608				05322V1
11								22-609				05322V1
12								22-610				05322V1
13								22-611				05322V1
14								22-612				05322V1
15								22-613				05322V1
16								22-614				05322V1
17								22-615				05322V1
18								22-616				05322V1
19								22-617				05322V1
20								22-618				05322V1
21								22-619				05322V1
22								22-620				05322V1
23								22-620				17322V1
24								22-621				17322V1
INV_A6082

Cell Formulas
Range	Formula
B3	B3	=H101
B4:D4	B4	=N101
B5:D5	B5	=T125
B6:D6	B6	=Z101
B7:D7	B7	=AF101
I3:I4	I3	=IFERROR(INDEX(STORAGE_DATA!$H:$H,AGGREGATE(15,6,ROW(STORAGE_DATA!$H$3:$H$200)/($I$1=TRIM(STORAGE_DATA!$D$3:$D$200))/($K$1=STORAGE_DATA!$G$3:$G$200)/($H$3=STORAGE_DATA!$E$3:$E$200)/($L$3=STORAGE_DATA!$F$3:$F$200),ROWS($I$3:$I3))),"")
I5:I8	I5	=IFERROR(INDEX(STORAGE_DATA!$H:$H,AGGREGATE(15,6,ROW(STORAGE_DATA!$H$3:$H$200)/($I$1=TRIM(STORAGE_DATA!$D$3:$D$200))/($K$1=STORAGE_DATA!$G$3:$G$200)/($H$3=STORAGE_DATA!$E$3:$E$200)/($L$3=STORAGE_DATA!$F$3:$F$200),ROWS($I$3:$I5))),"")
B9:D9	B9	=SUM(B3:B8)
H3:H24	H3	=IFERROR(INDEX(STORAGE_DATA!$E:$E,AGGREGATE(15,6,ROW(STORAGE_DATA!$E$3:$E$200)/($I$1=TRIM(STORAGE_DATA!$D$3:$D$200))/($K$1=STORAGE_DATA!$G$3:$G$200),ROWS($H$3:$H3))),"")
L3:L24	L3	=IFERROR(INDEX(STORAGE_DATA!$F:$F,AGGREGATE(15,6,ROW(STORAGE_DATA!$F$3:$F$200)/($I$1=TRIM(STORAGE_DATA!$D$3:$D$200))/($K$1=STORAGE_DATA!$G$3:$G$200),ROWS($L$3:$L3))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Data Validation
Cell	Allow	Criteria
I1	List	=All_Alloy
K1	List	=REM_1

 

[Kenor]

Hi All,

This entry actually has similarities to my previous entry.

Only this time, I tried to use the same formula to get data from multiple criteria.

I have amended the formula, but not sure it is correct or not.
(Having two criteria is fixed but the other two criteria are variable)

Need anyone's help to verify.

I use this formula to get data from another sheet:

=IFERROR(INDEX(STORAGE_DATA!$H:$H,AGGREGATE(15,6,ROW(STORAGE_DATA!$H$3:$H$200)/($I$1=TRIM(STORAGE_DATA!$D$3:$D$200))/($K$1=STORAGE_DATA!$G$3:$G$200)/($H$3=STORAGE_DATA!$E$3:$E$200)/($L$3=STORAGE_DATA!$F$3:$F$200),ROWS($I$3:$I3))),"")

Can refer to the below data table,

FG Database Bundle.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	SUMMARY → A6082							INVENTORY →	A6082		50
2	Ø	IN (pcs)	OUT (pcs)	TOTAL STOCK	Remark			BUNDLE NO	IN (pcs)	OUT (pcs)	TOTAL STOCK	LOT NO
3								22-602	6			03322V2
4		0	0	0				22-602				05322V1
5		0	0	0				22-603				05322V1
6		0	0	0				22-604				05322V1
7		0	0	0				22-605				05322V1
8								22-606				05322V1
9	TOTAL =	0	0	0				22-607				05322V1
10								22-608				05322V1
11								22-609				05322V1
12								22-610				05322V1
13								22-611				05322V1
14								22-612				05322V1
15								22-613				05322V1
16								22-614				05322V1
17								22-615				05322V1
18								22-616				05322V1
19								22-617				05322V1
20								22-618				05322V1
21								22-619				05322V1
22								22-620				05322V1
23								22-620				17322V1
24								22-621				17322V1
INV_A6082

Cell Formulas
Range	Formula
B3	B3	=H101
B4:D4	B4	=N101
B5:D5	B5	=T125
B6:D6	B6	=Z101
B7:D7	B7	=AF101
I3:I4	I3	=IFERROR(INDEX(STORAGE_DATA!$H:$H,AGGREGATE(15,6,ROW(STORAGE_DATA!$H$3:$H$200)/($I$1=TRIM(STORAGE_DATA!$D$3:$D$200))/($K$1=STORAGE_DATA!$G$3:$G$200)/($H$3=STORAGE_DATA!$E$3:$E$200)/($L$3=STORAGE_DATA!$F$3:$F$200),ROWS($I$3:$I3))),"")
I5:I8	I5	=IFERROR(INDEX(STORAGE_DATA!$H:$H,AGGREGATE(15,6,ROW(STORAGE_DATA!$H$3:$H$200)/($I$1=TRIM(STORAGE_DATA!$D$3:$D$200))/($K$1=STORAGE_DATA!$G$3:$G$200)/($H$3=STORAGE_DATA!$E$3:$E$200)/($L$3=STORAGE_DATA!$F$3:$F$200),ROWS($I$3:$I5))),"")
B9:D9	B9	=SUM(B3:B8)
H3:H24	H3	=IFERROR(INDEX(STORAGE_DATA!$E:$E,AGGREGATE(15,6,ROW(STORAGE_DATA!$E$3:$E$200)/($I$1=TRIM(STORAGE_DATA!$D$3:$D$200))/($K$1=STORAGE_DATA!$G$3:$G$200),ROWS($H$3:$H3))),"")
L3:L24	L3	=IFERROR(INDEX(STORAGE_DATA!$F:$F,AGGREGATE(15,6,ROW(STORAGE_DATA!$F$3:$F$200)/($I$1=TRIM(STORAGE_DATA!$D$3:$D$200))/($K$1=STORAGE_DATA!$G$3:$G$200),ROWS($L$3:$L3))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Data Validation
Cell	Allow	Criteria
I1	List	=All_Alloy
K1	List	=REM_1

I need a formula for column I starting from I3 and down

 

[Kenor]

To get data from 'STORAGE_DATA Sheet' below

FG Database Bundle.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	STORAGE_DATA										DataBase
2	Date	Time	PIC	Model	Bundle No	Lot No	Ø	Qty (pcs)		Soaking	Weight (kg)	Length (mm)
3	16/03/2022	10:10:56	SHAH	AHS-2P	22-002	11122V2 / 03222V1	69	17		HO	867	5000
4	16/03/2022	10:11:04	SHAH	AHS-2P	22-004	03222V2	79	15		HO	1003	5000
5	16/03/2022	10:11:06	SHAH	AHS-2P	22-004	03222V1	67	17		HO	818	5000
6	16/03/2022	10:11:07	SHAH	AHS-2P	22-005	03222V1	67	17		HO	818	5000
7	16/03/2022	10:11:09	SHAH	AHS-2P	22-011	04222V1	51	30		HO	836	5000
8	16/03/2022	10:11:10	SHAH	AHS-2P	22-012	04222V1	51	30		HO	836	5000
9	16/03/2022	10:10:56	SHAH	AHS-2P	22-002	11122V2 / 03222V1	69	17		HO	867	5000
10	16/03/2022	10:11:04	SHAH	AHS-2P	22-004	03222V2	79	15		HO	1003	5000
11	16/03/2022	10:11:06	SHAH	AHS-2P	22-004	03222V1	67	17		HO	818	5000
12	16/03/2022	10:11:07	SHAH	AHS-2P	22-005	03222V1	67	17		HO	818	5000
13	16/03/2022	10:11:09	SHAH	AHS-2P	22-011	04222V1	51	30		HO	836	5000
14	16/03/2022	10:11:10	SHAH	AHS-2P	22-012	04222V1	51	30		HO	836	5000
15	21/03/2022	14:10:21	SHAH	A6082	22-437	06322V1	52	18		H	506	4906
16	21/03/2022	14:10:23	SHAH	A6082	22-437	09322V2	52	5		H	141	4906
17	21/03/2022	14:10:24	SHAH	A6082	22-438	09322V2	52	23		H	647	4906
18	21/03/2022	14:10:25	SHAH	A6082	22-439	09322V2	52	23		H	647	4906
19	21/03/2022	14:10:25	SHAH	A6082	22-440	09322V2	52	23		H	647	4906
20	21/03/2022	14:10:26	SHAH	A6082	22-441	09322V2	52	23		H	647	4906
21	21/03/2022	14:10:27	SHAH	A6082	22-442	09322V2	52	23		H	647	4906
22	21/03/2022	14:10:28	SHAH	A6082	22-443	09322V2	52	23		H	647	4906
23	21/03/2022	14:10:29	SHAH	A6082	22-444	09322V2	52	23		H	647	4906
24	21/03/2022	14:10:30	SHAH	A6082	22-445	09322V2	52	23		H	647	4906
25	21/03/2022	14:10:31	SHAH	A6082	22-446	09322V2	52	23		H	647	4906
26	21/03/2022	14:10:32	SHAH	A6082	22-447	09322V2	52	23		H	647	4906
27	21/03/2022	14:10:33	SHAH	A6082	22-448	09322V2	52	23		H	647	4906
28	21/03/2022	14:10:34	SHAH	A6082	22-449	09322V2	52	23		H	647	4906
29	21/03/2022	14:10:35	SHAH	A6082	22-450	09322V2	52	23		H	647	4906
30	21/03/2022	14:10:36	SHAH	A6082	22-451	09322V2	52	23		H	647	4906
31	21/03/2022	14:10:37	SHAH	A6082	22-452	09322V2	52	23		H	647	4906
32	21/03/2022	14:10:38	SHAH	A6082	22-453	09322V2	52	23		H	647	4906
33	21/03/2022	14:10:38	SHAH	A6082	22-454	10322V2	52	23		H	647	4906
34	21/03/2022	14:10:39	SHAH	A6082	22-455	10322V2	52	23		H	647	4906
35	21/03/2022	14:10:40	SHAH	A6082	22-456	10322V2	52	23		H	647	4906
36	21/03/2022	14:10:41	SHAH	A6082	22-457	10322V2	52	23		H	647	4906
37	21/03/2022	14:10:42	SHAH	A6082	22-458	10322V2	52	23		H	647	4906
38	21/03/2022	14:10:43	SHAH	A6082	22-459	10322V2	52	23		H	647	4906
39	21/03/2022	14:10:44	SHAH	A6082	22-460	10322V2	52	23		H	647	4906
40	21/03/2022	14:10:45	SHAH	A6082	22-461	10322V2	52	23		H	647	4906
41	21/03/2022	14:10:46	SHAH	A6082	22-462	10322V2	52	23		H	647	4906
42	21/03/2022	14:10:46	SHAH	A6082	22-463	10322V2	52	23		H	647	4906
43	21/03/2022	14:10:47	SHAH	A6082	22-464	10322V2	52	23		H	647	4906
44	21/03/2022	14:10:48	SHAH	A6082	22-465	10322V2	52	23		H	647	4906
45	21/03/2022	14:10:49	SHAH	A6082	22-466	10322V2	52	23		H	647	4906
46	21/03/2022	14:10:50	SHAH	A6082	22-467	10322V2	52	23		H	647	4906
47	21/03/2022	14:10:51	SHAH	A6082	22-468	10322V2	52	23		H	647	4906
48	21/03/2022	14:10:52	SHAH	A6082	22-469	10322V2	52	23		H	647	4906
49	21/03/2022	14:10:53	SHAH	A6082	22-602	03322V2	50	6		H	154	4842
50	21/03/2022	14:10:54	SHAH	A6082	22-602	05322V1	50	17		H	436	4842
51	21/03/2022	14:10:55	SHAH	A6082	22-603	05322V1	50	23		H	590	4842
52	21/03/2022	14:10:55	SHAH	A6082	22-604	05322V1	50	23		H	590	4842
53	21/03/2022	14:10:56	SHAH	A6082	22-605	05322V1	50	23		H	590	4842
54	21/03/2022	14:10:57	SHAH	A6082	22-606	05322V1	50	23		H	590	4842
55	21/03/2022	14:10:58	SHAH	A6082	22-607	05322V1	50	23		H	590	4842
56	21/03/2022	14:10:59	SHAH	A6082	22-608	05322V1	50	23		H	590	4842
57	21/03/2022	14:11:00	SHAH	A6082	22-609	05322V1	50	23		H	590	4842
58	21/03/2022	14:11:01	SHAH	A6082	22-610	05322V1	50	23		H	590	4842
59	21/03/2022	14:11:02	SHAH	A6082	22-611	05322V1	50	23		H	590	4842
60	21/03/2022	14:11:03	SHAH	A6082	22-612	05322V1	50	23		H	590	4842
61	21/03/2022	14:11:04	SHAH	A6082	22-613	05322V1	50	23		H	590	4842
62	21/03/2022	14:11:05	SHAH	A6082	22-614	05322V1	50	23		H	590	4842
63	21/03/2022	14:11:06	SHAH	A6082	22-615	05322V1	50	23		H	590	4842
64	21/03/2022	14:11:07	SHAH	A6082	22-616	05322V1	50	23		H	590	4842
65	21/03/2022	14:11:07	SHAH	A6082	22-617	05322V1	50	23		H	590	4842
66	21/03/2022	14:11:09	SHAH	A6082	22-618	05322V1	50	23		H	590	4842
67	21/03/2022	14:11:10	SHAH	A6082	22-619	05322V1	50	23		H	590	4842
68	21/03/2022	14:11:11	SHAH	A6082	22-620	05322V1	50	11		H	282	4842
69	21/03/2022	14:11:11	SHAH	A6082	22-620	17322V1	50	12		H	308	4842
70	21/03/2022	14:11:12	SHAH	A6082	22-621	17322V1	50	23		H	590	4842
71	21/03/2022	14:11:13	SHAH	A6082	22-622	17322V1	50	23		H	590	4842
72	21/03/2022	14:11:14	SHAH	A6082	22-623	17322V1	50	23		H	590	4842
73	21/03/2022	14:11:15	SHAH	A6082	22-624	17322V1	50	23		H	590	4842
74	22/03/2022	14:11:11	TRY	A6082	22-625	17322V1	50	12		H	308	4842
75	22/03/2022	14:11:12	TRY	A6082	22-626	17322V1	50	23		H	590	4842
76	22/03/2022	14:11:13	TRY	A6082	22-627	17322V1	50	23		H	590	4842
77	22/03/2022	14:11:14	TRY	A6082	22-628	17322V1	50	23		H	590	4842
78	22/03/2022	14:11:15	TRY	A6082	22-629	17322V1	50	23		H	590	4842
STORAGE_DATA

 

[Kenor]

Can anyone help me in this?

 

[Kenor]

I just want to know why I find it difficult to get the value when using the same formula as I used for other Databases before.

Below is an example I did for my previous RM_Database_Trial_2. The formula works well.
Only the IN and OUT columns, I need to edit each row number when copying the same formula for the next row. It's a bit difficult for me..

RM_Database_Trial_2.xlsm
	S	T	U	V	W
1	INVENTORY →	A6082-2	POT
2	ID	NET WEIGHT (Kg) IN	NET WEIGHT (Kg) OUT	TOTAL STOCK	TRANSFER #
3	3:50:38	430.5	0	431	05/01/2021
4	3:52:50	395.5	395.5	0	05/01/2021
5	7:11:15	474.5	474.5	0	05/01/2021
6	14:24:59	411.5	411.5	0	05/01/2021
7	16:31:12	467.5	467.5	0	05/01/2021
8		0	0	0
9		0	0	0
10		0	0	0
11		0	0	0
12		0	0	0
13		0	0	0
14		0	0	0
15		0	0	0
16		0	0	0
17		0	0	0
18		0	0	0
19		0	0	0
20				0
21				0
22				0
23				0
24				0
25				0
Inventory-A6082-2

Cell Formulas
Range	Formula
T3	T3	=IFERROR(INDEX('Register IN'!$A$3:$K$1674,MATCH(1,('Inventory-A6082-2'!$S$3='Register IN'!$C$3:$C$1674)*('Inventory-A6082-2'!$W$3='Register IN'!$I$3:$I$1674)*('Inventory-A6082-2'!$T$1='Register IN'!$D$3:$D$1674)*('Inventory-A6082-2'!$U$1='Register IN'!$E$3:$E$1674),0),11),0)
U3	U3	=IFERROR(INDEX('Register OUT'!$A$3:$K$1997,MATCH(1,('Inventory-A6082-2'!$S$3='Register OUT'!$C$3:$C$1997)*('Inventory-A6082-2'!$W$3='Register OUT'!$I$3:$I$1997)*('Inventory-A6082-2'!$T$1='Register OUT'!$D$3:$D$1997)*('Inventory-A6082-2'!$U$1='Register OUT'!$E$3:$E$1997),0),11),0)
T4	T4	=IFERROR(INDEX('Register IN'!$A$3:$K$1674,MATCH(1,('Inventory-A6082-2'!$S$4='Register IN'!$C$3:$C$1674)*('Inventory-A6082-2'!$W$4='Register IN'!$I$3:$I$1674)*('Inventory-A6082-2'!$T$1='Register IN'!$D$3:$D$1674)*('Inventory-A6082-2'!$U$1='Register IN'!$E$3:$E$1674),0),11),0)
U4	U4	=IFERROR(INDEX('Register OUT'!$A$3:$K$1997,MATCH(1,('Inventory-A6082-2'!$S$4='Register OUT'!$C$3:$C$1997)*('Inventory-A6082-2'!$W$4='Register OUT'!$I$3:$I$1997)*('Inventory-A6082-2'!$T$1='Register OUT'!$D$3:$D$1997)*('Inventory-A6082-2'!$U$1='Register OUT'!$E$3:$E$1997),0),11),0)
T5	T5	=IFERROR(INDEX('Register IN'!$A$3:$K$1674,MATCH(1,('Inventory-A6082-2'!$S$5='Register IN'!$C$3:$C$1674)*('Inventory-A6082-2'!$W$5='Register IN'!$I$3:$I$1674)*('Inventory-A6082-2'!$T$1='Register IN'!$D$3:$D$1674)*('Inventory-A6082-2'!$U$1='Register IN'!$E$3:$E$1674),0),11),0)
U5	U5	=IFERROR(INDEX('Register OUT'!$A$3:$K$1997,MATCH(1,('Inventory-A6082-2'!$S$5='Register OUT'!$C$3:$C$1997)*('Inventory-A6082-2'!$W$5='Register OUT'!$I$3:$I$1997)*('Inventory-A6082-2'!$T$1='Register OUT'!$D$3:$D$1997)*('Inventory-A6082-2'!$U$1='Register OUT'!$E$3:$E$1997),0),11),0)
T6	T6	=IFERROR(INDEX('Register IN'!$A$3:$K$1674,MATCH(1,('Inventory-A6082-2'!$S$6='Register IN'!$C$3:$C$1674)*('Inventory-A6082-2'!$W$6='Register IN'!$I$3:$I$1674)*('Inventory-A6082-2'!$T$1='Register IN'!$D$3:$D$1674)*('Inventory-A6082-2'!$U$1='Register IN'!$E$3:$E$1674),0),11),0)
U6	U6	=IFERROR(INDEX('Register OUT'!$A$3:$K$1997,MATCH(1,('Inventory-A6082-2'!$S$6='Register OUT'!$C$3:$C$1997)*('Inventory-A6082-2'!$W$6='Register OUT'!$I$3:$I$1997)*('Inventory-A6082-2'!$T$1='Register OUT'!$D$3:$D$1997)*('Inventory-A6082-2'!$U$1='Register OUT'!$E$3:$E$1997),0),11),0)
T7	T7	=IFERROR(INDEX('Register IN'!$A$3:$K$1674,MATCH(1,('Inventory-A6082-2'!$S$7='Register IN'!$C$3:$C$1674)*('Inventory-A6082-2'!$W$7='Register IN'!$I$3:$I$1674)*('Inventory-A6082-2'!$T$1='Register IN'!$D$3:$D$1674)*('Inventory-A6082-2'!$U$1='Register IN'!$E$3:$E$1674),0),11),0)
U7	U7	=IFERROR(INDEX('Register OUT'!$A$3:$K$1997,MATCH(1,('Inventory-A6082-2'!$S$7='Register OUT'!$C$3:$C$1997)*('Inventory-A6082-2'!$W$7='Register OUT'!$I$3:$I$1997)*('Inventory-A6082-2'!$T$1='Register OUT'!$D$3:$D$1997)*('Inventory-A6082-2'!$U$1='Register OUT'!$E$3:$E$1997),0),11),0)
T8	T8	=IFERROR(INDEX('Register IN'!$A$3:$K$1674,MATCH(1,('Inventory-A6082-2'!$S$8='Register IN'!$C$3:$C$1674)*('Inventory-A6082-2'!$W$8='Register IN'!$I$3:$I$1674)*('Inventory-A6082-2'!$T$1='Register IN'!$D$3:$D$1674)*('Inventory-A6082-2'!$U$1='Register IN'!$E$3:$E$1674),0),11),0)
U8	U8	=IFERROR(INDEX('Register OUT'!$A$3:$K$1997,MATCH(1,('Inventory-A6082-2'!$S$8='Register OUT'!$C$3:$C$1997)*('Inventory-A6082-2'!$W$8='Register OUT'!$I$3:$I$1997)*('Inventory-A6082-2'!$T$1='Register OUT'!$D$3:$D$1997)*('Inventory-A6082-2'!$U$1='Register OUT'!$E$3:$E$1997),0),11),0)
T9	T9	=IFERROR(INDEX('Register IN'!$A$3:$K$1674,MATCH(1,('Inventory-A6082-2'!$S$9='Register IN'!$C$3:$C$1674)*('Inventory-A6082-2'!$W$9='Register IN'!$I$3:$I$1674)*('Inventory-A6082-2'!$T$1='Register IN'!$D$3:$D$1674)*('Inventory-A6082-2'!$U$1='Register IN'!$E$3:$E$1674),0),11),0)
U9	U9	=IFERROR(INDEX('Register OUT'!$A$3:$K$1997,MATCH(1,('Inventory-A6082-2'!$S$9='Register OUT'!$C$3:$C$1997)*('Inventory-A6082-2'!$W$9='Register OUT'!$I$3:$I$1997)*('Inventory-A6082-2'!$T$1='Register OUT'!$D$3:$D$1997)*('Inventory-A6082-2'!$U$1='Register OUT'!$E$3:$E$1997),0),11),0)
T10	T10	=IFERROR(INDEX('Register IN'!$A$3:$K$1674,MATCH(1,('Inventory-A6082-2'!$S$10='Register IN'!$C$3:$C$1674)*('Inventory-A6082-2'!$W$10='Register IN'!$I$3:$I$1674)*('Inventory-A6082-2'!$T$1='Register IN'!$D$3:$D$1674)*('Inventory-A6082-2'!$U$1='Register IN'!$E$3:$E$1674),0),11),0)
U10	U10	=IFERROR(INDEX('Register OUT'!$A$3:$K$1997,MATCH(1,('Inventory-A6082-2'!$S$10='Register OUT'!$C$3:$C$1997)*('Inventory-A6082-2'!$W$10='Register OUT'!$I$3:$I$1997)*('Inventory-A6082-2'!$T$1='Register OUT'!$D$3:$D$1997)*('Inventory-A6082-2'!$U$1='Register OUT'!$E$3:$E$1997),0),11),0)
T11	T11	=IFERROR(INDEX('Register IN'!$A$3:$K$1674,MATCH(1,('Inventory-A6082-2'!$S$11='Register IN'!$C$3:$C$1674)*('Inventory-A6082-2'!$W$11='Register IN'!$I$3:$I$1674)*('Inventory-A6082-2'!$T$1='Register IN'!$D$3:$D$1674)*('Inventory-A6082-2'!$U$1='Register IN'!$E$3:$E$1674),0),11),0)
U11	U11	=IFERROR(INDEX('Register OUT'!$A$3:$K$1997,MATCH(1,('Inventory-A6082-2'!$S$11='Register OUT'!$C$3:$C$1997)*('Inventory-A6082-2'!$W$11='Register OUT'!$I$3:$I$1997)*('Inventory-A6082-2'!$T$1='Register OUT'!$D$3:$D$1997)*('Inventory-A6082-2'!$U$1='Register OUT'!$E$3:$E$1997),0),11),0)
T12	T12	=IFERROR(INDEX('Register IN'!$A$3:$K$1674,MATCH(1,('Inventory-A6082-2'!$S$12='Register IN'!$C$3:$C$1674)*('Inventory-A6082-2'!$W$12='Register IN'!$I$3:$I$1674)*('Inventory-A6082-2'!$T$1='Register IN'!$D$3:$D$1674)*('Inventory-A6082-2'!$U$1='Register IN'!$E$3:$E$1674),0),11),0)
U12	U12	=IFERROR(INDEX('Register OUT'!$A$3:$K$1997,MATCH(1,('Inventory-A6082-2'!$S$12='Register OUT'!$C$3:$C$1997)*('Inventory-A6082-2'!$W$12='Register OUT'!$I$3:$I$1997)*('Inventory-A6082-2'!$T$1='Register OUT'!$D$3:$D$1997)*('Inventory-A6082-2'!$U$1='Register OUT'!$E$3:$E$1997),0),11),0)
T13	T13	=IFERROR(INDEX('Register IN'!$A$3:$K$1674,MATCH(1,('Inventory-A6082-2'!$S$13='Register IN'!$C$3:$C$1674)*('Inventory-A6082-2'!$W$13='Register IN'!$I$3:$I$1674)*('Inventory-A6082-2'!$T$1='Register IN'!$D$3:$D$1674)*('Inventory-A6082-2'!$U$1='Register IN'!$E$3:$E$1674),0),11),0)
U13	U13	=IFERROR(INDEX('Register OUT'!$A$3:$K$1997,MATCH(1,('Inventory-A6082-2'!$S$13='Register OUT'!$C$3:$C$1997)*('Inventory-A6082-2'!$W$13='Register OUT'!$I$3:$I$1997)*('Inventory-A6082-2'!$T$1='Register OUT'!$D$3:$D$1997)*('Inventory-A6082-2'!$U$1='Register OUT'!$E$3:$E$1997),0),11),0)
T14	T14	=IFERROR(INDEX('Register IN'!$A$3:$K$1674,MATCH(1,('Inventory-A6082-2'!$S$14='Register IN'!$C$3:$C$1674)*('Inventory-A6082-2'!$W$14='Register IN'!$I$3:$I$1674)*('Inventory-A6082-2'!$T$1='Register IN'!$D$3:$D$1674)*('Inventory-A6082-2'!$U$1='Register IN'!$E$3:$E$1674),0),11),0)
U14	U14	=IFERROR(INDEX('Register OUT'!$A$3:$K$1997,MATCH(1,('Inventory-A6082-2'!$S$14='Register OUT'!$C$3:$C$1997)*('Inventory-A6082-2'!$W$14='Register OUT'!$I$3:$I$1997)*('Inventory-A6082-2'!$T$1='Register OUT'!$D$3:$D$1997)*('Inventory-A6082-2'!$U$1='Register OUT'!$E$3:$E$1997),0),11),0)
T15	T15	=IFERROR(INDEX('Register IN'!$A$3:$K$1674,MATCH(1,('Inventory-A6082-2'!$S$15='Register IN'!$C$3:$C$1674)*('Inventory-A6082-2'!$W$15='Register IN'!$I$3:$I$1674)*('Inventory-A6082-2'!$T$1='Register IN'!$D$3:$D$1674)*('Inventory-A6082-2'!$U$1='Register IN'!$E$3:$E$1674),0),11),0)
U15	U15	=IFERROR(INDEX('Register OUT'!$A$3:$K$1997,MATCH(1,('Inventory-A6082-2'!$S$15='Register OUT'!$C$3:$C$1997)*('Inventory-A6082-2'!$W$15='Register OUT'!$I$3:$I$1997)*('Inventory-A6082-2'!$T$1='Register OUT'!$D$3:$D$1997)*('Inventory-A6082-2'!$U$1='Register OUT'!$E$3:$E$1997),0),11),0)
T16	T16	=IFERROR(INDEX('Register IN'!$A$3:$K$1674,MATCH(1,('Inventory-A6082-2'!$S$16='Register IN'!$C$3:$C$1674)*('Inventory-A6082-2'!$W$16='Register IN'!$I$3:$I$1674)*('Inventory-A6082-2'!$T$1='Register IN'!$D$3:$D$1674)*('Inventory-A6082-2'!$U$1='Register IN'!$E$3:$E$1674),0),11),0)
U16	U16	=IFERROR(INDEX('Register OUT'!$A$3:$K$1997,MATCH(1,('Inventory-A6082-2'!$S$16='Register OUT'!$C$3:$C$1997)*('Inventory-A6082-2'!$W$16='Register OUT'!$I$3:$I$1997)*('Inventory-A6082-2'!$T$1='Register OUT'!$D$3:$D$1997)*('Inventory-A6082-2'!$U$1='Register OUT'!$E$3:$E$1997),0),11),0)
T17	T17	=IFERROR(INDEX('Register IN'!$A$3:$K$1674,MATCH(1,('Inventory-A6082-2'!$S$17='Register IN'!$C$3:$C$1674)*('Inventory-A6082-2'!$W$17='Register IN'!$I$3:$I$1674)*('Inventory-A6082-2'!$T$1='Register IN'!$D$3:$D$1674)*('Inventory-A6082-2'!$U$1='Register IN'!$E$3:$E$1674),0),11),0)
U17	U17	=IFERROR(INDEX('Register OUT'!$A$3:$K$1997,MATCH(1,('Inventory-A6082-2'!$S$17='Register OUT'!$C$3:$C$1997)*('Inventory-A6082-2'!$W$17='Register OUT'!$I$3:$I$1997)*('Inventory-A6082-2'!$T$1='Register OUT'!$D$3:$D$1997)*('Inventory-A6082-2'!$U$1='Register OUT'!$E$3:$E$1997),0),11),0)
T18	T18	=IFERROR(INDEX('Register IN'!$A$3:$K$1674,MATCH(1,('Inventory-A6082-2'!$S$18='Register IN'!$C$3:$C$1674)*('Inventory-A6082-2'!$W$18='Register IN'!$I$3:$I$1674)*('Inventory-A6082-2'!$T$1='Register IN'!$D$3:$D$1674)*('Inventory-A6082-2'!$U$1='Register IN'!$E$3:$E$1674),0),11),0)
U18	U18	=IFERROR(INDEX('Register OUT'!$A$3:$K$1997,MATCH(1,('Inventory-A6082-2'!$S$18='Register OUT'!$C$3:$C$1997)*('Inventory-A6082-2'!$W$18='Register OUT'!$I$3:$I$1997)*('Inventory-A6082-2'!$T$1='Register OUT'!$D$3:$D$1997)*('Inventory-A6082-2'!$U$1='Register OUT'!$E$3:$E$1997),0),11),0)
T19	T19	=IFERROR(INDEX('Register IN'!$A$3:$K$1674,MATCH(1,('Inventory-A6082-2'!$S$19='Register IN'!$C$3:$C$1674)*('Inventory-A6082-2'!$W$19='Register IN'!$I$3:$I$1674)*('Inventory-A6082-2'!$T$1='Register IN'!$D$3:$D$1674)*('Inventory-A6082-2'!$U$1='Register IN'!$E$3:$E$1674),0),11),0)
U19	U19	=IFERROR(INDEX('Register OUT'!$A$3:$K$1997,MATCH(1,('Inventory-A6082-2'!$S$19='Register OUT'!$C$3:$C$1997)*('Inventory-A6082-2'!$W$19='Register OUT'!$I$3:$I$1997)*('Inventory-A6082-2'!$T$1='Register OUT'!$D$3:$D$1997)*('Inventory-A6082-2'!$U$1='Register OUT'!$E$3:$E$1997),0),11),0)
V3:V25	V3	=T3-U3
W3:W25	W3	=IFERROR(INDEX('Register IN'!$I$3:$I$1674,SMALL(IF(1=((--('Inventory-A6082-2'!$T$1='Register IN'!$D$3:$D$1674))*(--('Inventory-A6082-2'!$U$1='Register IN'!$E$3:$E$1674))),ROW('Register IN'!$I$3:$I$1674)-2,""),ROW()-2)),"")
S3:S25	S3	=IFERROR(INDEX('Register IN'!$C$3:$C$1674,SMALL(IF(1=((--('Inventory-A6082-2'!$T$1='Register IN'!$D$3:$D$1674))*(--('Inventory-A6082-2'!$U$1='Register IN'!$E$3:$E$1674))),ROW('Register IN'!$C$3:$C$1674)-2,""),ROW()-2)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Data Validation
Cell	Allow	Criteria
T1	List	=All_Alloy
U1	List	=REM_1

 

[Kenor]

I get the data for RM_Database_Trial_2 is based on Register IN & Register OUT sheet as below.

RM_Database_Trial_2.xlsm
	A	B	C	D	E	F		H	I	J	K
1	REGISTER IN										Database
2	Date	Time	ID	Alloy	Recycle Material	IN (Kg)		PIC	Transfer #	Remark *(Bin/Pallet)	Net Weight (Kg)
3	01/05/2021	3:47:37	3:50:38	A6082-2	POT	430.5		SHIVA	05/01/2021		431
4	01/05/2021	3:49:39	3:52:50	A6082-2	POT	395.5		SHIVA	05/01/2021		396
5	01/05/2021	7:00:54	7:03:02	A6082-2	CROP CASTING	1331.5		SHIVA	05/01/2021	350	982
6	01/05/2021	7:08:09	7:11:15	A6082-2	POT	474.5		SHIVA	05/01/2021		475
7	01/05/2021	7:25:10	7:28:00	A6082-2	CROP CASTING	1548.5		SHIVA	05/01/2021	350	1199
8	01/05/2021	7:31:46	7:34:31	MIX	CROP CASTING	710.5		SHIVA	05/01/2021	242	469
9	01/05/2021	11:25:10	11:28:08	AP-2	POT	431.5		FAUZAN	05/01/2021		432
10	01/05/2021	13:42:51	13:45:57	AP-2	POT	500		FAUZAN	05/01/2021		500
11	01/05/2021	14:22:58	14:24:59	A6082-2	POT	411.5		ANBARUL	05/01/2021		412
12	01/05/2021	16:28:29	16:31:12	A6082-2	POT	467.5		ANBARUL	05/01/2021		468
13	01/05/2021	18:27:38	18:30:30	A6082-2	CROP CASTING	1325		ANBARUL	05/01/2021	320	1005
14	01/05/2021	18:46:38	18:49:46	A6082-2	CROP CASTING	1341		ANBARUL	05/01/2021	320	1021
15	01/05/2021	18:50:18	18:53:29	A6082-2	DR	201.5		ANBARUL	05/01/2021	12	190
16	01/05/2021	20:39:19	20:40:58	A6082-2	DR	626		SHIVA	05/01/2021	350	276
17											0
18											0
19											0
20											0
Register IN

Cell Formulas
Range	Formula
K3:K20	K3	=F3-J3

 

[Kenor]

RM_Database_Trial_2.xlsm
	A	B	C	D	E		G	H	I	J	K
1	REGISTER OUT
2	Date	Time	ID	Alloy	Recycle Material		OUT (Kg)	PIC	Transfer #	Remark *(Bin/Pallet)	Net Weight (Kg)
3	30/04/2021	17:40:06	7:04:32	A6082-2	POT		385	FAUZAN	30/04/2021		385
4	30/04/2021	17:40:21	7:07:01	A6082-2	POT		395.5	FAUZAN	30/04/2021		396
5	30/04/2021	18:04:35	18:07:34	A6082-2	POT		386.5	ANBARUL	30/04/2021		387
6	30/04/2021	18:05:28	18:07:34	A6082-2	POT		386.5	ANBARUL	30/04/2021		387
7	01/05/2021	11:25:25	11:28:08	AP-2	POT		431.5	FAUZAN	05/01/2021		432
8	01/05/2021	13:43:20	13:45:57	AP-2	POT		500	FAUZAN	05/01/2021		500
9	01/05/2021	14:22:26	14:24:59	A6082-2	POT		411.5	ANBARUL	05/01/2021		412
10	01/05/2021	17:09:02	14:18:15	A6082-2	DR		124.5	FAUZAN	28/04/2021	12	113
11	01/05/2021	17:09:13	18:15:31	A6082-2	CROP CASTING		1438.5	FAUZAN	30/04/2021	320	1119
12	01/05/2021	17:09:21	20:22:19	A6082-2	POT		298	FAUZAN	20/04/2021		298
13	01/05/2021	17:09:33	7:28:00	A6082-2	CROP CASTING		1548.5	FAUZAN	05/01/2021	350	1199
14	01/05/2021	17:09:45	7:03:02	A6082-2	CROP CASTING		1331.5	FAUZAN	05/01/2021	350	982
15	01/05/2021	17:10:01	18:23:00	A6082-2	CROP CASTING		1288	FAUZAN	30/04/2021	232	1056
16	01/05/2021	17:10:16	18:47:56	A6082-2	DR		102.5	FAUZAN	27/04/2021	12	91
17	01/05/2021	17:10:24	18:47:50	A6082-2	DR		91	FAUZAN	30/04/2021		91
18	01/05/2021	17:11:10	15:16:13	AP-2	DR		192	FAUZAN	30/04/2021		192
19	01/05/2021	17:11:24	15:57:29	A6061-9	POT		319	FAUZAN	29/04/2021		319
20	01/05/2021	17:11:42	11:53:23	AP-2	POT		284	FAUZAN	30/04/2021		284
21	01/05/2021	17:11:55	11:28:08	AP-2	AP-2		431.5	FAUZAN	05/01/2021		432
22	01/05/2021	17:12:11	9:00:33	AHS-2	BAR CASTING		855	FAUZAN	27/04/2021		855
23	01/05/2021	17:12:36	13:45:57	POT	POT		500	FAUZAN	05/01/2021		500
24	01/05/2021	17:13:03	16:34:57	A6061-9	BAR CASTING		710	FAUZAN	29/04/2021		710
25	01/05/2021	17:14:55	18:32:38	AP-2	BRI-Q		846	FAUZAN	20/04/2021		846
26	01/05/2021	17:15:06	4:55:36	AP-2	BRI-Q STM		675	FAUZAN	21/04/2021		675
27	01/05/2021	17:15:17	4:49:13	BRI-Q STM	AP-2		683.5	FAUZAN	21/04/2021		684
28	01/05/2021	17:16:03	20:12:22	A6082-2	POT		423	FAUZAN	27/04/2021		423
29	01/05/2021	17:16:23	20:13:49	A6082-2	POT		437	FAUZAN	27/04/2021		437
30	01/05/2021	17:16:36	20:19:41	A6082-2	POT		414.5	FAUZAN	20/04/2021		415
31	01/05/2021	17:16:49	4:22:28	A6082-2	POT		489.5	FAUZAN	20/04/2021		490
32	01/05/2021	17:17:11	17:04:00	A6082-2	448.5		448.5	FAUZAN	20/04/2021		449
33	01/05/2021	17:17:41	16:27:15	A6082-2	POT		338.5	FAUZAN	20/04/2021		339
34	01/05/2021	17:18:07	19:13:34	A6082-2	POT		463	FAUZAN	20/04/2021		463
35	01/05/2021	17:18:23	7:16:31	A6082-2	BAR CASTING		410	FAUZAN	20/04/2021		410
36	01/05/2021	17:19:21	7:35:53	A6082	POT		420.5	FAUZAN	21/04/2021		421
Register OUT

Cell Formulas
Range	Formula
K3:K36	K3	=G3-J3

 

[Kenor]

Hi All,

This entry actually has similarities to my previous entry.

Only this time, I tried to use the same formula to get data from multiple criteria.

I have amended the formula, but not sure it is correct or not.
(Having two criteria is fixed but the other two criteria are variable)

Need anyone's help to verify.

I use this formula to get data from another sheet:

=IFERROR(INDEX(STORAGE_DATA!$H:$H,AGGREGATE(15,6,ROW(STORAGE_DATA!$H$3:$H$200)/($I$1=TRIM(STORAGE_DATA!$D$3:$D$200))/($K$1=STORAGE_DATA!$G$3:$G$200)/($H$3=STORAGE_DATA!$E$3:$E$200)/($L$3=STORAGE_DATA!$F$3:$F$200),ROWS($I$3:$I3))),"")

Can refer to the below data table,

FG Database Bundle.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	SUMMARY → A6082							INVENTORY →	A6082		50
2	Ø	IN (pcs)	OUT (pcs)	TOTAL STOCK	Remark			BUNDLE NO	IN (pcs)	OUT (pcs)	TOTAL STOCK	LOT NO
3								22-602	6			03322V2
4		0	0	0				22-602				05322V1
5		0	0	0				22-603				05322V1
6		0	0	0				22-604				05322V1
7		0	0	0				22-605				05322V1
8								22-606				05322V1
9	TOTAL =	0	0	0				22-607				05322V1
10								22-608				05322V1
11								22-609				05322V1
12								22-610				05322V1
13								22-611				05322V1
14								22-612				05322V1
15								22-613				05322V1
16								22-614				05322V1
17								22-615				05322V1
18								22-616				05322V1
19								22-617				05322V1
20								22-618				05322V1
21								22-619				05322V1
22								22-620				05322V1
23								22-620				17322V1
24								22-621				17322V1
INV_A6082

Cell Formulas
Range	Formula
B3	B3	=H101
B4:D4	B4	=N101
B5:D5	B5	=T125
B6:D6	B6	=Z101
B7:D7	B7	=AF101
I3:I4	I3	=IFERROR(INDEX(STORAGE_DATA!$H:$H,AGGREGATE(15,6,ROW(STORAGE_DATA!$H$3:$H$200)/($I$1=TRIM(STORAGE_DATA!$D$3:$D$200))/($K$1=STORAGE_DATA!$G$3:$G$200)/($H$3=STORAGE_DATA!$E$3:$E$200)/($L$3=STORAGE_DATA!$F$3:$F$200),ROWS($I$3:$I3))),"")
I5:I8	I5	=IFERROR(INDEX(STORAGE_DATA!$H:$H,AGGREGATE(15,6,ROW(STORAGE_DATA!$H$3:$H$200)/($I$1=TRIM(STORAGE_DATA!$D$3:$D$200))/($K$1=STORAGE_DATA!$G$3:$G$200)/($H$3=STORAGE_DATA!$E$3:$E$200)/($L$3=STORAGE_DATA!$F$3:$F$200),ROWS($I$3:$I5))),"")
B9:D9	B9	=SUM(B3:B8)
H3:H24	H3	=IFERROR(INDEX(STORAGE_DATA!$E:$E,AGGREGATE(15,6,ROW(STORAGE_DATA!$E$3:$E$200)/($I$1=TRIM(STORAGE_DATA!$D$3:$D$200))/($K$1=STORAGE_DATA!$G$3:$G$200),ROWS($H$3:$H3))),"")
L3:L24	L3	=IFERROR(INDEX(STORAGE_DATA!$F:$F,AGGREGATE(15,6,ROW(STORAGE_DATA!$F$3:$F$200)/($I$1=TRIM(STORAGE_DATA!$D$3:$D$200))/($K$1=STORAGE_DATA!$G$3:$G$200),ROWS($L$3:$L3))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Data Validation
Cell	Allow	Criteria
I1	List	=All_Alloy
K1	List	=REM_1

I really need someone to help me to get correct formula for column I & J...??