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

K

Kenor

Board Regular
Joined
Dec 8, 2020
Messages
116
Office Version
  1. 2016
Platform
  1. Windows
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

1648612090407.png


To 'INV_A6082' sheet

1648612212332.png
 
Sort by date Sort by votes
This is a sample workbook that I use the same formula and it works well..

RM_Database_Trial_2.xlsm
STUVW
1INVENTORY →A6082-2POT
2IDNET WEIGHT (Kg) IN NET WEIGHT (Kg) OUTTOTAL STOCKTRANSFER #
33:50:38430.5043105/01/2021
43:52:50395.5395.5005/01/2021
57:11:15474.5474.5005/01/2021
614:24:59411.5411.5005/01/2021
716:31:12467.5467.5005/01/2021
8 000 
9 000 
10 000 
11 000 
12 000 
13 000 
14 000 
15 000 
16 000 
17 000 
Inventory-A6082-2
Cell Formulas
RangeFormula
S3:S17S3=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)),"")
T3T3=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)
U3U3=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)
V3:V17V3=T3-U3
W3:W17W3=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)),"")
T4T4=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)
U4U4=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)
T5T5=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)
U5U5=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)
T6T6=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)
U6U6=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)
T7T7=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)
U7U7=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)
T8T8=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)
U8U8=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)
T9T9=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)
U9U9=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)
T10T10=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)
U10U10=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)
T11T11=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)
U11U11=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)
T12T12=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)
U12U12=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)
T13T13=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)
U13U13=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)
T14T14=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)
U14U14=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)
T15T15=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)
U15U15=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)
T16T16=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)
U16U16=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)
T17T17=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)
U17U17=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)
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Data Validation
CellAllowCriteria
T1List=All_Alloy
U1List=REM_1
 
Upvote 0

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
jasonb75 said:
Like I said, no data meeting the criteria.

You're looking for a value of 50 in column G and it doesn't exist.
Click to expand...
Actually there is but on the bottom lines

FG Database Bundle.xlsm
ABCDEFGHIJKL
1STORAGE_DATADataBase
2DateTimePICModelBundle NoLot NoØQty (pcs)SoakingWeight (kg)Length (mm)
4921/03/202214:10:53SHAHA6082 22-602 03322V2 506H 1544842
5021/03/202214:10:54SHAHA6082 22-602 05322V1 5017H 4364842
5121/03/202214:10:55SHAHA6082 22-603 05322V1 5023H 5904842
5221/03/202214:10:55SHAHA6082 22-604 05322V1 5023H 5904842
5321/03/202214:10:56SHAHA6082 22-605 05322V1 5023H 5904842
5421/03/202214:10:57SHAHA6082 22-606 05322V1 5023H 5904842
5521/03/202214:10:58SHAHA6082 22-607 05322V1 5023H 5904842
5621/03/202214:10:59SHAHA6082 22-608 05322V1 5023H 5904842
5721/03/202214:11:00SHAHA6082 22-609 05322V1 5023H 5904842
5821/03/202214:11:01SHAHA6082 22-610 05322V1 5023H 5904842
5921/03/202214:11:02SHAHA6082 22-611 05322V1 5023H 5904842
6021/03/202214:11:03SHAHA6082 22-612 05322V1 5023H 5904842
6121/03/202214:11:04SHAHA6082 22-613 05322V1 5023H 5904842
6221/03/202214:11:05SHAHA6082 22-614 05322V1 5023H 5904842
6321/03/202214:11:06SHAHA6082 22-615 05322V1 5023H 5904842
6421/03/202214:11:07SHAHA6082 22-616 05322V1 5023H 5904842
6521/03/202214:11:07SHAHA6082 22-617 05322V1 5023H 5904842
6621/03/202214:11:09SHAHA6082 22-618 05322V1 5023H 5904842
6721/03/202214:11:10SHAHA6082 22-619 05322V1 5023H 5904842
STORAGE_DATA
 
Upvote 0
It would be best to get rid of the trailing spaces, but you could use
Excel Formula: 
=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))),"")
 
Upvote 0
Solution
Fluff said:
It would be best to get rid of the trailing spaces, but you could use
Excel Formula: 
=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))),"")
Click to expand...

Thanks Fluff...it works ! :)
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
To list down data from another sheet using IFERROR formula with multiple criteria
Replies
7
Views
633
Kenor
K
K
  • Solved
Index with Multiple Conditions
Replies
2
Views
110
kivikatz
K
tezza
  • Solved
Dynamically extend list help
Replies
3
Views
91
Cubist
Cubist
A
  • Solved
List All Rows in Database which Belong to X Employee
Replies
4
Views
154
Fluff
Fluff
E
  • Solved
All Unique Combinations from 5 Columns of Data + Customizations - Excel 365
Replies
6
Views
241
Excel Jason
E

Forum statistics

Threads
1,224,825
Messages
6,181,191
Members
453,021
Latest member
pingpong7117

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top