Create new lists from an array in Excel

[JCK01]

Hi,

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

e.g. 

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

[#2]

@JCK01 

See the attached demo workbook.

[#3]

@JCK01 

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.

[#4]

@HansVogelaar 

Many thanks!

[#5]

@JCK01 

As variant

=LET(
    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)),
            LAMBDA(a,v,
                HSTACK(a, clear(CHOOSECOLS(byHeader,v) )
                )
            )
        ),,1),
    VSTACK( header, IFNA( res, "" ) )
)