Create new lists from an array in Excel

Copper Contributor


I would like help building a formula that creates a new list of values from an array containing a reference value in example below find all "a", "b" & "c" and populate the list with the corresponding  Col 2 value


Col1     Col2        new list "a"     new list "b"    new list "c"

a             1                1                        5                    3

a             2                2                        4                    2

b             5                2                                              3

c             3                5  

b            4

c             2

c             3

a             2

a             5


Thanks All

4 Replies


See the attached demo workbook.


An alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.

@Hans Vogelaar 


Many thanks!


As variant

    first, CHOOSECOLS(data, 1),
    values, CHOOSECOLS(data,2),
    header, TRANSPOSE( UNIQUE( first )),
    clear, LAMBDA(col, TOCOL( IF(col=0, NA(), col ),3 ) ),
    byHeader, (first=header)*values,
    res, DROP(
        REDUCE("", SEQUENCE(COLUMNS(header)),
                HSTACK(a, clear(CHOOSECOLS(byHeader,v) )
    VSTACK( header, IFNA( res, "" ) )