To list down data from another sheet using IFERROR formula with INDEX, SMALL and ROW function

[Kenor]

Hi All,

Need anyone helps. What's wrong with the below formula?

I want to use this formula

=IFERROR(INDEX(STORAGE_DATA!$E$3:$E$200,SMALL(IF(1=((--(INV_A6082!$I$1=STORAGE_DATA!$D$3:$D$200))*(--(INV_A6082!$K$1=STORAGE_DATA!$G$3:$G$200))),ROW(STORAGE_DATA!$E$3:$E$200)-4,""),ROW()-4)),"")

To list down data based on multiple criteria from the below 'STORAGE_DATA' sheet

To 'INV_A6082' sheet

 

[Kenor]

I would seem to be invisible today

Please see #6

 

[Kenor]

For your information,

I have used the same formula before in other workbooks and it works just fine.

Only this time, try using it in another workbook. Looks like there's a problem. I'm trying to find the reason.

 

[Fluff]

Sorry, I already answered your question

My apologies, I didn't see that reply.

Your formula should be

=IFERROR(INDEX(STORAGE_DATA!$E$3:$E$200,SMALL(IF(1=((--(INV_A6082!$I$1=STORAGE_DATA!$D$3:$D$200))*(--(INV_A6082!$K$1=STORAGE_DATA!$G$3:$G$200))),ROW(STORAGE_DATA!$E$3:$E$200)-row(STORAGE_DATA!$E$3)+1,""),ROW(H$3:H3))),"")

although the formula that Jason posted should work

 

[Kenor]

My apologies, I didn't see that reply.

Your formula should be

=IFERROR(INDEX(STORAGE_DATA!$E$3:$E$200,SMALL(IF(1=((--(INV_A6082!$I$1=STORAGE_DATA!$D$3:$D$200))*(--(INV_A6082!$K$1=STORAGE_DATA!$G$3:$G$200))),ROW(STORAGE_DATA!$E$3:$E$200)-row(STORAGE_DATA!$E$3)+1,""),ROW(H$3:H3))),"")

although the formula that Jason posted should work

Sorry, it's also the same as Jason posted.

No error but no data coming out.

 

[Fluff]

In that case can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[jasonb75]

No error but no data coming out.

If that is the case then it means that there is no data meeting the criteria.

 

[Kenor]

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		0	0	0
4		0	0	0
5		0	0	0
6		0	0	0
7		0	0	0
8
9	TOTAL =	0	0	0
10
11
12
13
14
15
16
17
18
INV_A6082

Cell Formulas
Range	Formula
B3:C3	B3	=H101
D3	D3	=B3-C3
B4:D4	B4	=N101
B5:D5	B5	=T125
B6:D6	B6	=Z101
B7:D7	B7	=AF101
H3	H3	=IFERROR(INDEX(STORAGE_DATA!$E$3:$E$200,SMALL(IF(1=((--(INV_A6082!$I$1=STORAGE_DATA!$D$3:$D$200))*(--(INV_A6082!$K$1=STORAGE_DATA!$G$3:$G$200))),ROW(STORAGE_DATA!$E$3:$E$200)-ROW(STORAGE_DATA!$E$3)+1,""),ROW(H$3:H3))),"")
H4:H6	H4	=IFERROR(INDEX(STORAGE_DATA!$E$3:$E$200,SMALL(IF(1=((--(INV_A6082!$I$1=STORAGE_DATA!$D$3:$D$200))*(--(INV_A6082!$K$1=STORAGE_DATA!$G$3:$G$200))),ROW(STORAGE_DATA!$E$3:$E$200)-ROW(STORAGE_DATA!$E$3)+1,""),ROW(H$3:H4))),"")
H7	H7	=IFERROR(INDEX(STORAGE_DATA!$E$3:$E$200,SMALL(IF(1=((--(INV_A6082!$I$1=STORAGE_DATA!$D$3:$D$200))*(--(INV_A6082!$K$1=STORAGE_DATA!$G$3:$G$200))),ROW(STORAGE_DATA!$E$3:$E$200)-4,""),ROW()-4)),"")
B9:D9	B9	=SUM(B3:B8)
Press CTRL+SHIFT+ENTER to enter array formulas.

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

 

[Kenor]

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
STORAGE_DATA

 

[jasonb75]

Like I said, no data meeting the criteria.

You're looking for a value of 50 in column G and it doesn't exist.

 

[Fluff]

Thanks for that.
The first problem I see is that col D has spaces after the values which I1 doesn't.