Array formula help

[helpme1233]

Hello all

I was wondering if you could please help me. I created a spreadsheet and have got 1 array formula working but I can't seem to replicate it.

I want to be able to find the highest loss figure (will be a minus figure) and again the highest figure at physical variance (again will be a net figure)

The table in the bottom with the total number of purchases has an array formula that works on finding the products just based on the terms "glassware", "silver" "crockery" and I want to replicate that across the top tables so that I don't have to manually update cell numbers.

Is anybody able to have a look and let me know what I'm doing wrong?

I would hugely appreciate the help.

[Pete_UK]

I just get a lot of errors when I open your file, presumably because you have deleted some rows from the sheet called 'Item Details 2016'. I presume you want your array formula to go into cell C3 of the Report sheet, and if so you can use this:

=SMALL(IF('Stock Sheet'!$A$3:$A$34=LOOKUP("zzzz",$A$2:$A3),'Stock Sheet'!$K$3:$K$34),B3)

You must commit this using the key combination of Ctrl-Shift-Enter, and then you can copy it down into the appropriate cells.

Hope this helps.

Pete

[helpme1233]

Hey Pete

Thank you so much. That wasn't quite it.

I'm basically trying to copy the formula I have in the bottom table. =IFERROR(INDEX('Stock Sheet'!F$3:F$204,SMALL(IF(('Stock Sheet'!A$3:A$204=A$20)*('Stock Sheet'!F$3:F$204>0),ROW('Stock Sheet'!A$3:A$204)-MIN(ROW('Stock Sheet'!A$3:A$204))+1),ROWS($1:1))),"")

And make that work in the top charts. I've tried to copy it but you can see it gives me a figure of -£28.00 which is still incorrect as there is a greater minus figure of -£381.78 which it wont pick up and I don't understand why. I'm basically looking for a way to elimate me having to update cell numbers each month

I've reattached my spreadsheet with my rubbish excel skills.

Any help would be really appreciated.

[FDibbins]

Hi, welcome to the forum

Stock sheet
To begin with, if all you are doing is referencing a single cell (anywhere), just use
='Item Details 2016'!A3
not
='Item Details 2016'!A:A
Same for B and C?

[helpme1233]

Thank you for the welcome

I'm not referencing a single cell.

I'm basically trying to look through all the glassware and find the one that is the highest cost variance and then find the product that goes with it. What I don't want to do is to change the cell numbers each month as I add new products in each month so I just want it to always look in glassware and find those, if that makes sense.

In the bottom chart - total number of purchases, it works perfectly! I just cant get it to work in the others.

[FDibbins]

I'm not referencing a single cell.

In Stock sheet A3 and B3 down, yes, you are.

OK, if you were doing this manually, what would you do?

[helpme1233]

Sorry I'm using the report sheet not the stock sheet. Everythign I'm working on is on the report sheet.

[FDibbins]

Yes, I understand that, I was just making an observation about the Stock sheet

OK, if you were doing this manually, what would you do?

[helpme1233]

Sorry - total misunderstanding on my part.

I'd look in the glassware and look at what product is the highest in terms of loss and in terms of quantity and manually fill that in.
Same thing with crockery and same thing with silver.