Search code examples
excelarray-formulas

excel - conditionally adding values and outputting a list without blanks

Overview:

Col-A = has bunch of values

Col-B = has the desired values

Col-C = a list of all the desired values 

Col-A Col-B Col-C
V1    x     V1
V2          V2
V3    x     V5
V4
V5    x

Desired:

Display Col-C values through a formula

Solution

  • We use a helper column to avoid array formulas. In D2 enter:

    =IF(B2="x",1+MAX($D$1:D1),"")

    and copy down. Then in C2 enter:

    =IFERROR(INDEX(A:A,MATCH(ROWS($1:1),D:D,0)),"")

    and copy down:

    enter image description here

    EDIT#1:

    In C2 place the array formula:

    =IFERROR(INDEX($A$1:$A$9,SMALL(IF($B$2:$B$9<>"",ROW($B$2:$B$9)),ROW(1:1))),"")

    and copy down:

    enter image description here