Array formula problem.

**coolhit** · 07-05-2009, 06:43 AM

Hi,

I have writen the following formula in cell C8 but i don't get any result.

=IF(ISERROR(INDEX('Consumables In AM Rack'!$B$4:$B$357,SMALL(IF('Consumables In AM Rack'!$A$4:$A$357=$C$3,ROW('Consumables In AM Rack'!$A$4:$A$357),""),ROW()))),"",INDEX('Consumables In AM Rack'!$B$4:$B$357,SMALL(IF('Consumables In AM Rack'!$A$4:$A$357=$C$3,ROW('Consumables In AM Rack'!$A$4:$A$357),""),ROW()))).

In Basic.
User enters code in cell C3 (press enter)
Array formula looksup the value in Consumables In AM Rack sheet column A bring over all the list of location it matches in column B one by one.

result i should see in Location Search Sheet.

for code 24959
cell C8 should have AM351002
cell C9 should have AM353002
cell C10 should have AM354002

For code SW09
cell C8 should have AM351001
cell C9 should have AM352002
cell C10 should have AM353001

Please can you assist me.

Many Thanks in advance.

Regards
Hitesh

**Domenic** · 07-05-2009, 08:09 AM

Try...

A8:

=COUNTIF('Consumables In AM Rack'!$A$4:$A$357,$C$3)

C8, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ROWS(C$8:C8)<=$A$8,INDEX('Consumables In AM Rack'!$B$5:$B$356,SMALL(IF('Consumables In AM Rack'!$A$5:$A$356=$C$3,ROW('Consumables In AM Rack'!$A$5:$A$356)-ROW('Consumables In AM Rack'!$A$5)+1),ROWS(C$8:C8))),"")

See sample file attached...

**windknife** · 07-05-2009, 09:11 AM

Change your array formula,

=IF(ISERROR(INDEX('Consumables In AM Rack'!$B$4:$B$357,SMALL(IF('Consumables In AM Rack'!$A$4:$A$357=$C$3,ROW('Consumables In AM Rack'!$A$4:$A$357),""),ROW(1:1)))),"",INDEX('Consumables In AM Rack'!$B$4:$B$357,SMALL(IF('Consumables In AM Rack'!$A$4:$A$357=$C$3,ROW('Consumables In AM Rack'!$A$4:$A$357),""),ROW(1:1))))

HTH,
windknife

**contaminated** · 08-09-2009, 05:47 PM

In C8

=IF(ROWS($B$8:B8)<=COUNTIF('Consumables In AM Rack'!$C$5:$C$356,'Location Search'!$C$3),INDEX('Consumables In AM Rack'!$B$5:$B$356,SMALL(IF($C$3='Consumables In AM Rack'!$C$5:$C$356,ROW(INDIRECT("1:"&ROWS('Consumables In AM Rack'!5:356)))),ROW(A1))),"")

In D8

=IF(ROWS($B$8:C8)<=COUNTIF('Consumables In AM Rack'!$C$5:$C$356,'Location Search'!$C$3),INDEX('Consumables In AM Rack'!$E$5:$E$356,SMALL(IF($C$3='Consumables In AM Rack'!$C$5:$C$356,ROW(INDIRECT("1:"&ROWS('Consumables In AM Rack'!5:356)))),ROW(B1))),"")

Copy 'em down

**martindwilson** · 08-09-2009, 06:11 PM

id use
=IF(ROWS($A$1:A1)>COUNTIF('Consumables In AM Rack'!$A$1:$A$356,$C$3),"",INDEX('Consumables In AM Rack'!$B$1:$B$356,SMALL(IF('Consumables In AM Rack'!$A$1:$A$356=$C$3,ROW('Consumables In AM Rack'!$A$1:$A$356),""),ROWS($A$1:A1))))