Lookup names that are in another list but have them listed one row after another

[AlexK_]

Hello,

Hoping someone can assist me with this excel question.

I would like to create List3 as it looks. If the color in List2 is in List1, then I want it to show up in List3. The part I am stuck on is getting List3 to pull green to the first row,, blue to the second row, and so on.

Appreciate the time you spend assisting me.

Thank you.
Alex

List1	List2	List3
red	brown	green
yellow	brown	blue
orange	brown	black
blue	green
green	brown
purple	brown
black	blue
white	black

 

[alansidman]

With Power Query aka Get and Transform Data and found on the Data Tab of the Ribbon

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Duplicates" = Table.Distinct(Source),
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Removed Duplicates1" = Table.Distinct(Source2),
    MQ = Table.NestedJoin(#"Removed Duplicates", {"List1"}, #"Removed Duplicates1" , {"List2"},"List3",JoinKind.LeftOuter),
    #"Expanded List3" = Table.ExpandTableColumn(MQ, "List3", {"List2"}, {"List2"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded List3", each ([List2] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"List1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"List2", "List3"}})

in
    #"Renamed Columns"

Book3
	A	B	C	D
1	List1	List2		List3
2	red	brown		green
3	yellow	brown		blue
4	orange	brown		black
5	blue	green
6	green	brown
7	purple	brown
8	black	blue
9	white	black
Sheet1

 

[kevin9999]

With 365:

Book1
	A	B	C
1	ist1	List2	List3
2	red	brown	green
3	yellow	brown	blue
4	orange	brown	black
5	blue	green
6	green	brown
7	purple	brown
8	black	blue
9	white	black
Sheet3

Cell Formulas
Range	Formula
C2:C4	C2	=FILTER(B:B,COUNTIF(A:A,B:B)>0,"")
Dynamic array formulas.

 

[AlexK_]

THANK YOU !! Filter array formula is so powerful

 

[kevin9999]

Happy to help & thanks for the feedback