Two "identical" array formulas, one works but the other doesn't, please help!

[Angiebourke]

So I am using a spare sheet (Workings -hide) to pull info from my main sheets into a list format. I have successfully done this on rows 3-33 using the array formula

=IFERROR(INDEX('Invoice queries'!$C$4:$C$200,SMALL(IF('Workings -hide'!$B$1='Invoice queries'!$B$4:$B$200,ROW('Invoice queries'!$C$4:$C$200)-3," "),ROW()-2)), "")

This nicely pulls any invoice numbers that are listed onto the Invoice queries sheet into a list under each supplier name.

I am now trying to do the same thing in cells 35-85 to pull info from the "With purchasing" sheet. I am using this formula

=IFERROR(INDEX('With purchasing'!$C$4:$C$200,SMALL(IF('Workings -hide'!$B$1='With purchasing'!$B$4:$B$200,ROW('With purchasing'!$C$4:$C$200)-3," "),ROW()-2)), "")

I have no idea why it's not working but I also don't really understand the formula in the first place.

Please help! Both the With purchasing and Invoice queries sheets have the company names listed from B4 downwards and the invoice numbers from C4 downwards.

[Angiebourke]

I have just moved the second array onto a separate sheet instead of putting it on the Workings -hide sheet and now it works. Could anybody explain why?

[Estevaoba]

Change the K argument "ROW()-2" of the SMALL function to ROWS(A$1:A1):

Please Login or Register  to view this content.

At row 3, ROW() = 3. So ROW()-2 = 1, i.e., the first row with relevant data.
At row 35, ROW() = 35. So ROW()-2 at that row results 35 - 2, i.e., the 33rd row with relevant data.

Good luck!