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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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
To list down data from another sheet using IFERROR formula with multiple criteria
Replies
7
Views
406
Kenor
K
G
  • Question
INDEX Functions with multiple criteria
Replies
5
Views
241
Phuoc
P
VinceF
  • Question
VBA To Copy & Paste Value
Replies
0
Views
147
VinceF
VinceF
J
  • Question
Formula to move information to calendar
Replies
0
Views
70
jc352
J
S
  • Question
Unexpected and inconsistent error with ranking rows based on criteria
Replies
2
Views
96
strugglingaccountant
S

Forum statistics

Threads
1,214,601
Messages
6,120,467
Members
448,965
Latest member
grijken

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