index array formula problem

vipulgos

vipulgos

Active Member
Joined
Aug 17, 2002
Messages
335
Office Version
  1. 2010
Platform
  1. Windows
Dear all,,
I am using the formula for accounting purpose which extract data from other sheet (company) in the same book,but autofilling the cells below in sheet called "bank", when there is no match in original sheet(company) , it writes #ref! error and so the Autosum below will also gives me error.
I want excel to help me like this:(company)
If there is error(#ref!), it should automatically write 0 in that column, so my Autosum will sum only the data actually extracted


Excel 2003
ABCDE
1
2CREDITDEBIT
3AmountCame FromAmountPaid forTo
4
511abc` 100stamp papermisc.
6300efg` 96xyzbank
7300hij#REF!
8250xyz#REF!
9#REF!#REF!
10#REF!#REF!
11#REF!
12#REF!
13
14
15
16#REF!#REF!

<tbody>
</tbody>
BANK
​

Worksheet Formulas
CellFormula
A16=SUBTOTAL(9,A4:A15)
C16=SUBTOTAL(9,C5:C15)

<tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
C5{=INDEX(company!$F$6:$J$990,SMALL(IF(company!$I$6:$I$990="bank",ROW(company!$F$6:$J$990)-ROW(company!$I$6)+1,ROW(company!I100)+1),G1),2)}
D5{=INDEX(company!$F$6:$J$990,SMALL(IF(company!$I$6:$I$990="bank",ROW(company!$F$6:$J$990)-ROW(company!$I$6)+1,ROW(company!I100)+1),G1),3)}
E5{=INDEX(company!$F$6:$J$990,SMALL(IF(company!$I$6:$I$990="bank",ROW(company!$F$6:$J$990)-ROW(company!$I$6)+1,ROW(company!F100)+1),G1),1)}
C6{=INDEX(company!$F$6:$J$990,SMALL(IF(company!$I$6:$I$990="bank",ROW(company!$F$6:$J$990)-ROW(company!$I$6)+1,ROW(company!I1001)+1),G2),2)}
D6{=INDEX(company!$F$6:$J$990,SMALL(IF(company!$I$6:$I$990="bank",ROW(company!$F$6:$J$990)-ROW(company!$I$6)+1,ROW(company!J100)+1),G2),3)}
E6{=INDEX(company!$F$6:$J$990,SMALL(IF(company!$I$6:$I$990="bank",ROW(company!$F$6:$J$990)-ROW(company!$I$6)+1,ROW(company!J100)+1),G2),1)}
C7{=INDEX(company!$F$6:$J$990,SMALL(IF(company!$I$6:$I$990="bank",ROW(company!$F$6:$J$990)-ROW(company!$I$6)+1,ROW(company!I1002)+1),G3),2)}
C8{=INDEX(company!$F$6:$J$990,SMALL(IF(company!$I$6:$I$990="bank",ROW(company!$F$6:$J$990)-ROW(company!$I$6)+1,ROW(company!I1003)+1),G4),2)}
C9{=INDEX(company!$F$6:$J$990,SMALL(IF(company!$I$6:$I$990="bank",ROW(company!$F$6:$J$990)-ROW(company!$I$6)+1,ROW(company!I1004)+1),G5),2)}
C10{=INDEX(company!$F$6:$J$990,SMALL(IF(company!$I$6:$I$990="bank",ROW(company!$F$6:$J$990)-ROW(company!$I$6)+1,ROW(company!I1005)+1),G6),2)}
A5{=INDEX(company!$A$6:$E$983,SMALL(IF(company!$A$6:$E$983="bank",ROW(company!$A$6:$E$983)-ROW(company!$A$6)+1,ROW(company!$F$103)+1),G1),1)}
B5{=INDEX(company!$A$6:$E$983,SMALL(IF(company!$A$6:$E$983="bank",ROW(company!$A$6:$E$983)-ROW(company!$A$6)+1,ROW(company!F100)+1),G1),2)}
A6{=INDEX(company!$A$6:$E$983,SMALL(IF(company!$A$6:$E$983="bank",ROW(company!$A$6:$E$983)-ROW(company!$A$6)+1,ROW(company!$F$103)+1),G2),1)}
B6{=INDEX(company!$A$6:$E$983,SMALL(IF(company!$A$6:$E$983="bank",ROW(company!$A$6:$E$983)-ROW(company!$A$6)+1,ROW(company!F101)+1),G2),2)}
A7{=INDEX(company!$A$6:$E$983,SMALL(IF(company!$A$6:$E$983="bank",ROW(company!$A$6:$E$983)-ROW(company!$A$6)+1,ROW(company!$F$103)+1),G3),1)}
B7{=INDEX(company!$A$6:$E$983,SMALL(IF(company!$A$6:$E$983="bank",ROW(company!$A$6:$E$983)-ROW(company!$A$6)+1,ROW(company!F102)+1),G3),2)}
A8{=INDEX(company!$A$6:$E$983,SMALL(IF(company!$A$6:$E$983="bank",ROW(company!$A$6:$E$983)-ROW(company!$A$6)+1,ROW(company!$F$1003)+1),G4),1)}
B8{=INDEX(company!$A$6:$E$983,SMALL(IF(company!$A$6:$E$983="bank",ROW(company!$A$6:$E$983)-ROW(company!$A$6)+1,ROW(company!F103)+1),G4),2)}
A9{=INDEX(company!$A$6:$E$983,SMALL(IF(company!$A$6:$E$983="bank",ROW(company!$A$6:$E$983)-ROW(company!$A$6)+1,ROW(company!$F$1003)+1),G5),1)}
A10{=INDEX(company!$A$6:$E$983,SMALL(IF(company!$A$6:$E$983="bank",ROW(company!$A$6:$E$983)-ROW(company!$A$6)+1,ROW(company!$F$1003)+1),G6),1)}
A11{=INDEX(company!$A$6:$E$983,SMALL(IF(company!$A$6:$E$983="bank",ROW(company!$A$6:$E$983)-ROW(company!$A$6)+1,ROW(company!$F$1003)+1),G7),1)}
A12{=INDEX(company!$A$6:$E$983,SMALL(IF(company!$A$6:$E$983="bank",ROW(company!$A$6:$E$983)-ROW(company!$A$6)+1,ROW(company!$F$1003)+1),G8),1)}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Company sheet is as under

Excel 2003
ABCDEFGHIJ
711.00abccompanybankfor misc. expensemisc.11.00xyzcompanyxyz
825.00defcompanyCashfor misc. expensemic11.00abccompanyxyz
9300.00efgcompanybankfor misc. expensemisc.120.00plan copiescompanyxyz
10300.00hijcompanybankfor misc. expensemisc.200.00plan copiescompanyxyz
11250.00mnocompanyCashfor misc. expensemisc.100.00stamp paperbankxyz
12250.00xyzbankCashfor misc. expensemisc.400.00stamp papercompanyxyz
13bank96.00xyzbankxyz
14misc.70.00Rubber stampcompanyxyz
15misc.40.00pan card courier chargecompanyxyz
16

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
company​
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
vipulgos said:
Dear all,,
I am using the formula for accounting purpose which extract data from other sheet (company) in the same book,but autofilling the cells below in sheet called "bank", when there is no match in original sheet(company) , it writes #ref! error and so the Autosum below will also gives me error.
I want excel to help me like this:(company)
If there is error(#ref!), it should automatically write 0 in that column, so my Autosum will sum only the data actually extracted


Excel 2003
ABCDE
1
2CREDITDEBIT
3AmountCame FromAmountPaid forTo
4
511abc` 100stamp papermisc.
6300efg` 96xyzbank
7300hij#REF!
8250xyz#REF!
9#REF!#REF!
10#REF!#REF!
11#REF!
12#REF!
13
14
15
16#REF!#REF!

<tbody>
</tbody>
BANK
​

Worksheet Formulas
CellFormula
A16=SUBTOTAL(9,A4:A15)
C16=SUBTOTAL(9,C5:C15)

<tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
C5{=INDEX(company!$F$6:$J$990,SMALL(IF(company!$I$6:$I$990="bank",ROW(company!$F$6:$J$990)-ROW(company!$I$6)+1,ROW(company!I100)+1),G1),2)}
D5{=INDEX(company!$F$6:$J$990,SMALL(IF(company!$I$6:$I$990="bank",ROW(company!$F$6:$J$990)-ROW(company!$I$6)+1,ROW(company!I100)+1),G1),3)}
E5{=INDEX(company!$F$6:$J$990,SMALL(IF(company!$I$6:$I$990="bank",ROW(company!$F$6:$J$990)-ROW(company!$I$6)+1,ROW(company!F100)+1),G1),1)}
C6{=INDEX(company!$F$6:$J$990,SMALL(IF(company!$I$6:$I$990="bank",ROW(company!$F$6:$J$990)-ROW(company!$I$6)+1,ROW(company!I1001)+1),G2),2)}
D6{=INDEX(company!$F$6:$J$990,SMALL(IF(company!$I$6:$I$990="bank",ROW(company!$F$6:$J$990)-ROW(company!$I$6)+1,ROW(company!J100)+1),G2),3)}
E6{=INDEX(company!$F$6:$J$990,SMALL(IF(company!$I$6:$I$990="bank",ROW(company!$F$6:$J$990)-ROW(company!$I$6)+1,ROW(company!J100)+1),G2),1)}
C7{=INDEX(company!$F$6:$J$990,SMALL(IF(company!$I$6:$I$990="bank",ROW(company!$F$6:$J$990)-ROW(company!$I$6)+1,ROW(company!I1002)+1),G3),2)}
C8{=INDEX(company!$F$6:$J$990,SMALL(IF(company!$I$6:$I$990="bank",ROW(company!$F$6:$J$990)-ROW(company!$I$6)+1,ROW(company!I1003)+1),G4),2)}
C9{=INDEX(company!$F$6:$J$990,SMALL(IF(company!$I$6:$I$990="bank",ROW(company!$F$6:$J$990)-ROW(company!$I$6)+1,ROW(company!I1004)+1),G5),2)}
C10{=INDEX(company!$F$6:$J$990,SMALL(IF(company!$I$6:$I$990="bank",ROW(company!$F$6:$J$990)-ROW(company!$I$6)+1,ROW(company!I1005)+1),G6),2)}
A5{=INDEX(company!$A$6:$E$983,SMALL(IF(company!$A$6:$E$983="bank",ROW(company!$A$6:$E$983)-ROW(company!$A$6)+1,ROW(company!$F$103)+1),G1),1)}
B5{=INDEX(company!$A$6:$E$983,SMALL(IF(company!$A$6:$E$983="bank",ROW(company!$A$6:$E$983)-ROW(company!$A$6)+1,ROW(company!F100)+1),G1),2)}
A6{=INDEX(company!$A$6:$E$983,SMALL(IF(company!$A$6:$E$983="bank",ROW(company!$A$6:$E$983)-ROW(company!$A$6)+1,ROW(company!$F$103)+1),G2),1)}
B6{=INDEX(company!$A$6:$E$983,SMALL(IF(company!$A$6:$E$983="bank",ROW(company!$A$6:$E$983)-ROW(company!$A$6)+1,ROW(company!F101)+1),G2),2)}
A7{=INDEX(company!$A$6:$E$983,SMALL(IF(company!$A$6:$E$983="bank",ROW(company!$A$6:$E$983)-ROW(company!$A$6)+1,ROW(company!$F$103)+1),G3),1)}
B7{=INDEX(company!$A$6:$E$983,SMALL(IF(company!$A$6:$E$983="bank",ROW(company!$A$6:$E$983)-ROW(company!$A$6)+1,ROW(company!F102)+1),G3),2)}
A8{=INDEX(company!$A$6:$E$983,SMALL(IF(company!$A$6:$E$983="bank",ROW(company!$A$6:$E$983)-ROW(company!$A$6)+1,ROW(company!$F$1003)+1),G4),1)}
B8{=INDEX(company!$A$6:$E$983,SMALL(IF(company!$A$6:$E$983="bank",ROW(company!$A$6:$E$983)-ROW(company!$A$6)+1,ROW(company!F103)+1),G4),2)}
A9{=INDEX(company!$A$6:$E$983,SMALL(IF(company!$A$6:$E$983="bank",ROW(company!$A$6:$E$983)-ROW(company!$A$6)+1,ROW(company!$F$1003)+1),G5),1)}
A10{=INDEX(company!$A$6:$E$983,SMALL(IF(company!$A$6:$E$983="bank",ROW(company!$A$6:$E$983)-ROW(company!$A$6)+1,ROW(company!$F$1003)+1),G6),1)}
A11{=INDEX(company!$A$6:$E$983,SMALL(IF(company!$A$6:$E$983="bank",ROW(company!$A$6:$E$983)-ROW(company!$A$6)+1,ROW(company!$F$1003)+1),G7),1)}
A12{=INDEX(company!$A$6:$E$983,SMALL(IF(company!$A$6:$E$983="bank",ROW(company!$A$6:$E$983)-ROW(company!$A$6)+1,ROW(company!$F$1003)+1),G8),1)}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Company sheet is as under

Excel 2003
ABCDEFGHIJ
711.00abccompanybankfor misc. expensemisc.11.00xyzcompanyxyz
825.00defcompanyCashfor misc. expensemic11.00abccompanyxyz
9300.00efgcompanybankfor misc. expensemisc.120.00plan copiescompanyxyz
10300.00hijcompanybankfor misc. expensemisc.200.00plan copiescompanyxyz
11250.00mnocompanyCashfor misc. expensemisc.100.00stamp paperbankxyz
12250.00xyzbankCashfor misc. expensemisc.400.00stamp papercompanyxyz
13bank96.00xyzbankxyz
14misc.70.00Rubber stampcompanyxyz
15misc.40.00pan card courier chargecompanyxyz
16

<tbody>
</tbody>
company​
Click to expand...

Your bottom table does not have headers so not sure which column are going to use in your top table but this will go like this:
company

*ABCDEFGHIJ
1**********
2**********
3**********
4**********
5**********
6**********
711abccompanybankfor misc. expensemisc.11xyzcompanyxyz
825defcompanyCashfor misc. expensemic11abccompanyxyz
9300efgcompanybankfor misc. expensemisc.120plan copiescompanyxyz
10300hijcompanybankfor misc. expensemisc.200plan copiescompanyxyz
11250mnocompanyCashfor misc. expensemisc.100stamp paperbankxyz
12250xyzbankCashfor misc. expensemisc.400stamp papercompanyxyz
13*****bank96xyzbankxyz
14*****misc.70Rubber stampcompanyxyz
15*****misc.40pan card courier chargecompanyxyz
16**********

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

bank

*ABCDEFG
1CREDIT*DEBIT**3*
2AmountCame FromAmountPaid forTo**
311abccompanybankfor misc. expense**
4300efgcompanybankfor misc. expense**
5300hijcompanybankfor misc. expense**
6*******
7*******
8*******

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
F1=COUNTIF(company!$D$7:$D$11000,"bank")
A3{=IF(ROWS($A$3:A3)>$F$1,"",INDEX(company!A$7:A$1000,SMALL(IF(company!$D$7:$D$1000="bank",ROW($D$7:$D$1000)-ROW($D$7)+1),ROWS($A$3:A3))))}
B3{=IF(ROWS($A$3:B3)>$F$1,"",INDEX(company!B$7:B$1000,SMALL(IF(company!$D$7:$D$1000="bank",ROW($D$7:$D$1000)-ROW($D$7)+1),ROWS($A$3:B3))))}
C3{=IF(ROWS($A$3:C3)>$F$1,"",INDEX(company!C$7:C$1000,SMALL(IF(company!$D$7:$D$1000="bank",ROW($D$7:$D$1000)-ROW($D$7)+1),ROWS($A$3:C3))))}
D3{=IF(ROWS($A$3:D3)>$F$1,"",INDEX(company!D$7:D$1000,SMALL(IF(company!$D$7:$D$1000="bank",ROW($D$7:$D$1000)-ROW($D$7)+1),ROWS($A$3:D3))))}
E3{=IF(ROWS($A$3:E3)>$F$1,"",INDEX(company!E$7:E$1000,SMALL(IF(company!$D$7:$D$1000="bank",ROW($D$7:$D$1000)-ROW($D$7)+1),ROWS($A$3:E3))))}

<tbody>
</tbody>
Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Code used for comparison between 2 list.
Replies
3
Views
318
Tupe77
T
R
  • Solved
Compare similar company names between two tables and return one of the values
Replies
2
Views
361
realtoast
R
E
  • Solved
FILTER function to return ONLY records with most recent date
Replies
5
Views
420
Fluff
Fluff
J
  • Question
index and Countif
Replies
2
Views
177
Fluff
Fluff
G
  • Question
VBA - add formula to cell when created
Replies
2
Views
297
Golaidron
G

Forum statistics

Threads
1,215,124
Messages
6,123,189
Members
449,090
Latest member
bes000

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