Lookup but ignore zero values

[drluke]

Sheet 1
	A	B	C	D	E
1	North	8	10.00	A001	9
2	North	9	0.00	A001
3	North	7	30.00	A002
4	West	9	50.00	A006
5	West	8	80.00	A012
6	West	9	0.00	A004

<colgroup><col width="62" style="width: 47pt;" span="6"> <tbody> </tbody>

In sheet 2 at A1 I need a formula that will give me all the values in col D of shee1 where col B = E1 but ignoring rows that are zero in col C.

Any advice would be much appreciated.

 

[Scott T]

Copy down as need

Book1
	A
1	50
Sheet2

Cell Formulas
Range	Formula
A1	{=IF(ROWS(A$1:A1)>COUNTIFS(Sheet1!$C$1:$C$7,Sheet1!$F$1,Sheet1!$D$1:$D$7,">0"),"",INDEX(Sheet1!$D$1:$D$7,SMALL(IF(Sheet1!$C$1:$C$7=Sheet1!$F$1,IF(Sheet1!$D$1:$D$7>0,ROW(Sheet1!$D$1:$D$7)-ROW(Sheet1!$D$1)+1)),ROWS(A$1:A1))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[drluke]

Thank you for this. My bad as I didn't explain properly. I need a list in sheet 2 starting from A1 that will give me all the values in sheet 1 col D where col B = E1 but skip zero values in col C. So I would expect to see the following in sheet 2 A1-A2

A001
A006

 

[MARZIOTULLIO]

Why A001 ?

Sheet2

A1=IFERROR(INDEX(Sheet1!$D$1:$D$6,SMALL(IF(Sheet1!$B$1:$B$6=Sheet1!$E$1,IF(Sheet1!$C$1:$C$6<>Sheet1!$C$2,ROW(Sheet1!$D$1:$D$6)-ROW(Sheet1!$D$1)+1)),ROWS(Sheet1!$D$1:Sheet1!D1))),"") control +shift+enter copy down

 

[Aladin Akyurek]

In A1 of Sheet2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Sheet1!$D$1:$D$6,SMALL(IF(Sheet1!$B$1:$B$6=Sheet1!$E$1,IF(ISNUMBER(1/Sheet1!$C$1:$C$6),ROW(Sheet1!$D$1:$D$6)-ROW(Sheet1!$D$1)+1)),ROWS($A$1:A1))),"")