Stock count based of matching description if serial number is in stock.

[conwaythibodeaux]

I am trying to determine how many items are left in stock based on whether the serial number is here or not.

This is a rough setup of how my sheet will be setup.

Sheet1
Column A will be a description, there could be 100s of different names and it will be in a random order, while many can and will be repeated at different times.
Column C will be the serial numbers assigned. This will be stepped by 1.
Column E is quantity in stock. 0 is not and 1 is yes.

Sheet2

In cell B4, I am wanting it to give me the value from Sheet1 Column C if Column E from the same row has a value of 1 based on matching the value from Sheet 2 cell A3.
In cell B5, I want it to do the same as cell B4 but ignore the return from B4 and get the next value from column C if there is a 1 in column E.
Cells B6-B23 would be the same as B5, giving the next value from column C based on E and A3.
If there is no returned value, I would like it to stay blank because in B4 I am wanting to use "=COUNT(B4:B23)"

Columns A and B are setup with the expected outcome.
Column D is being used as a test column.

I have this formula to get me started "=INDEX(Sheet1!C:C,MATCH($A$3,Sheet1!A:A,0))" but it only returns the first instance of the match and I am not too sure how to proceed.


Any help would be greatly appreciated.

[Bo_Ry]

Please try at
B4
=IFERROR(INDEX(Sheet1!C:C,AGGREGATE(15,6,ROW(Sheet1!A$7:A$36)/(Sheet1!$A$7:$A$34=LOOKUP("z",A$3:A4))/(Sheet1!$E$7:$E$36=1),ROWS(A$3:A4)-MATCH("z",A$3:A4))),"")

D4
=IFERROR(INDEX(Sheet1!C:C,AGGREGATE(15,6,ROW(Sheet1!A$7:A$36)/(Sheet1!$A$7:$A$34=LOOKUP("z",A$3:A4))/(Sheet1!$E$7:$E$36=0),ROWS(A$3:A4)-MATCH("z",A$3:A4))),"")

[conwaythibodeaux]

Thank you Bo_Ry,
It worked perfectly as suggested.

I went a little further and modified the formula to reflect the entire columns as the list will grow.
=IFERROR(INDEX(Sheet1!C:C,AGGREGATE(15,6,ROW(Sheet1!A:A)/(Sheet1!A:A=LOOKUP("z",A$3:A4))/(Sheet1!$E:E=1),ROWS(A$3:A4)-MATCH("z",A$3:A4))),"")