Excel Formula: Skip zero values in column when repeating Indexed values N times in array?

562 Views Asked by At

What function can I add to my G2 formula to skip names with zero values (Col E)?

        A       B       C       D       E       G       
    1   John                            0       SUE
    2   Sue             2:00    3:00    2       SUE
    3   Dan                             0       JOE
    4   Joe     1:00                    1       -
    5                                           -     


Formula in G1: Uses index to find first name with non-zero value in Col E
{=INDEX(A1:A20,MATCH(TRUE,E1:E20<>0,0))}

Formula in G2: Lists names N times based on value in Col E (but stops listing names as soon as it encounters a zero)
{=IFERROR(IF(COUNTIF($G$1:G1,G1)=INDEX(E:E,MATCH(G1,A:A,0)),IF(AND(INDEX(A:A,MATCH(G1,A:A,0)+1)<>0,INDEX(E:E,MATCH(G1,A:A,0)+1)<>0),INDEX(A:A,MATCH(G1,A:A,0)+1),"-"),G1),"-")}

0

There are 0 best solutions below