正如我所评论的，您可以使用Excel 2010+和Microsoft 365中提供的Power Query来完成此操作。

示例M编码：

let

//Read in the tables
    Source = Excel.CurrentWorkbook(){[Name="Types"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product Type Code", type text}, {"Description", type text}}),
    Source2 = Excel.CurrentWorkbook(){[Name="Colors"]}[Content],
    #"Changed Type2" = Table.TransformColumnTypes(Source2,{{"Color Code", type text}, {"Color Name", type text}}),

//Add Custom Column to Types with all the color codes
    #"Add Colors" = Table.AddColumn(#"Changed Type", "Colors", each #"Changed Type2"),
    #"Expanded Colors" = Table.ExpandTableColumn(#"Add Colors", "Colors", {"Color Code", "Color Name"}, {"Color Code", "Color Name"}),

//Concatenate code and color
    #"Concat" = Table.FromColumns(
    {List.Transform(List.Zip({#"Expanded Colors"[Product Type Code], #"Expanded Colors"[Color Code]}), each Text.Combine(_,"-"))}
   & {List.Transform(List.Zip({#"Expanded Colors"[Color Name], #"Expanded Colors"[Description]}), each Text.Combine(_," "))},
   type table[Product=text, Description=text])

in
    #"Concat"

您的样本结果

如果你有Excel 365，你也可以使用公式：

=LET(pt,tblProductType, cc,tblColorCode,
cntRowsPt,ROWS(pt),cntRowsCC,ROWS(cc),
MAKEARRAY(cntRowsPt*cntRowsCC,2,LAMBDA(r,c,
  IF(c=1,
     INDEX(pt,INT(ROUNDUP(r/cntRowsCC,0)),1) & "-" & INDEX(cc,MOD(r-1,cntRowsCC)+1,1),
   INDEX(cc,MOD(r-1,cntRowsCC)+1,2) & " " & INDEX(pt,INT(ROUNDUP(r/cntRowsCC,0)),2))
)))

基于公式的替代方法：

=HSTACK(
    TOCOL(TOROW(tblProductType[Product Type Code]) & "-" & tblColorCode[Color Code]),
    TOCOL(tblColorCode[Color Name] & " " & TOROW(tblProductType[Description]))
)