Formula to return item for first non occurrence of zero

[thelad]

Hi,

I want to return first occurrence of non zero in column C per customer ID (Column A) So for example:

I want the formula to return 3 and 16 for customer ID 5 and 6 (very right is the result). Basically for customer ID 5 they had zero for product ID 1 and 2 so product ID 3 is the first non zero amount for this customer.

Can this be done?

Customer ID	Product ID	Amount		Customer	Product ID
5	1​	0.00		5​	3​
5	2​	0.00		6​	16​
5	3​	50.00
5	4​	150.00
6	14​	0.00
6	15​	0.00
6	16​	230,349,112.64

 

[Toadstool]

Hi TheLad,

If you update your profile with your Excel version you may get a better answer. Mine uses Excel 2016.

TheLad.xlsx
	A	B	C	D	E	F
1	Customer ID	Product ID	Amount		Customer	Product ID
2	5	1	0		5	3
3	5	2	0		6	16
4	5	3	50
5	5	4	150
6	6	14	0
7	6	15	0
8	6	16	230,349,112.64
Sheet1

Cell Formulas
Range	Formula
F2:F8	F2	=IFERROR(INDEX($B$2:$B$9999,AGGREGATE(15,6,ROW($A$2:$A$9999)-ROW($A$1)/(($A$2:$A$9999=E2)*($C$2:$C$9999>0)),1)),"")

 

[thelad]

Hi, This worked..Thank you. I do not understand the aggregate formula?

 

[Toadstool]

You're welcome!

OK, so INDEX wants the row number of the first non-zero cell for that Customer.

AGGREGATE has several functions but I'm using the SMALL function (15) and saying ignore errors (6) to get the Kth lowest number from a range. I'm using its K parameter of 1 because we only want the first row.
AGGREGATE(15,6, .... ,1)

I'm not using the SMALL option to look at the contents of cells but rather to go through row numbers ($A$2:$A$9999)-ROW($A$1), which means it looks through 1 to 9998.

I divide into the row number (/) the result of (($A$2:$A$9999=E2)*($C$2:$C$9999>0))
So this checks the contents of $A$2:$A$9999 to match the Customer number in column E and multiplies by $C$2:$C$9999 being greater than zero.
If either check is FALSE (logical 0) I get the result 0*0=0, or 1*0=0, or 0*1=0 which divided into the row number gives a #DIV/0 error, but the AGGREGATE 6 option says ignore errors so it checks the next row.
When both are TRUE (logical 1) then 1*1=1 which is divided into the row number and AGGREGATE returns that row number to the INDEX.