Group Text by Keyword

let
  Group = Table.Buffer(
    Table.UnpivotOtherColumns(Excel.CurrentWorkbook(){[Name = "Table2"]}[Content], {}, "G", "V")
  ), 
  Ans = Table.AddColumn(
    Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
    "Ans", 
    each Text.Combine(
      List.Sort(
        List.Distinct(
          List.Combine(
            List.Transform(
              Text.Split(Text.Proper([Text]), " "), 
              (w) => Table.SelectRows(Group, each [V] = w)[G]
            )
          )
        )
      ), 
      ", "
    )
  )
in
  Ans

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  T2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content], 
  Groups = Table.UnpivotOtherColumns(T2, {}, "A", "V"), 
  S = Table.TransformRows(
    Source, 
    (l) =>
      Text.Combine(
        List.Select(
          Table.ColumnNames(T2), 
          each Table.RowCount(
            Table.SelectRows(
              Groups, 
              (r) =>
                r[A]
                  = _ and List.Contains(Text.Split(l[Text], " "), r[V], Comparer.OrdinalIgnoreCase)
            )
          )
            > 0
        ), 
        ", "
      )
  )
in
  S

let
  Unpivot = Table.UnpivotOtherColumns(Table2, {}, "Group", "Value"), 
  Sol = Table.AddColumn(
    Table1, 
    "Answer", 
    each Text.Combine(
      List.Distinct(
        List.Sort(
          List.RemoveNulls(
            List.Transform(
              {0 .. List.Count(Unpivot[Value]) - 1}, 
              (x) =>
                if List.Contains(
                  Text.Split([Text], " "), 
                  Unpivot[Value]{x}, 
                  Comparer.OrdinalIgnoreCase
                )
                then
                  Unpivot[Group]{x}
                else
                  null
            )
          )
        )
      ), 
      ", "
    )
  )
in
  Sol

let
  Fonte = Tabela2, 
  pv = Table.UnpivotOtherColumns(Fonte, {}, "Atributo", "Valor"), 
  tip = Table.TransformColumnTypes(pv, {{"Valor", type text}}), 
  tab = Table.FuzzyNestedJoin(
    Tabela1, 
    {"Text"}, 
    tip, 
    {"Valor"}, 
    "Personalizar", 
    JoinKind.LeftOuter, 
    [IgnoreCase = true, IgnoreSpace = true, Threshold = 0.05]
  ), 
  res = Table.AddColumn(
    tab, 
    "Resposta", 
    each [
      a = List.Transform([Personalizar][Atributo], each Text.Split(_, " "){1}), 
      b = Text.Combine(
        List.Transform(
          List.Distinct(List.Transform({a}, each List.Sort(_)){0}), 
          each Text.Combine({"Group ", _})
        ), 
        ", "
      )
    ][b]
  )[[Text], [Resposta]]
in
  res

let
  Source = Data, 
  Output = Table.AddColumn(
    Source, 
    "Expected", 
    each [
      w = Text.Split(Text.Proper([Text]), " "), 
      x = Table.ToColumns(Group), 
      y = Table.ColumnNames(Group), 
      z = Text.Combine(
        List.Transform(
          {0 .. List.Count(y) - 1}, 
          each if List.Count(List.Intersect({w, x{_}})) > 0 then y{_} else null
        ), 
        ", "
      )
    ][z]
  )
in
  Output

=MAP(A2:A11,LAMBDA(a,ARRAYTOTEXT(UNIQUE(TOCOL(IF(XMATCH(D2:G9,TEXTSPLIT(a," ")),D1:G1),3,1)))))

=MAP(A2:A11,LAMBDA(a,TEXTJOIN(", ",,FILTER(D1:G1,BYCOL(D2:G10,LAMBDA(c,COLUMNS(TEXTSPLIT(PROPER(a),TAKE(c,COUNTA(c))))))>1))))

=MAP(A2:A11,LAMBDA(x,ARRAYTOTEXT(UNIQUE(TOCOL(IF(SEARCH(D2:G9,x)/(D2:G9>0),D1:G1),2,1)))))

=MAP(A2:A10,LAMBDA(a,LET(e,D2:G9,ARRAYTOTEXT("Group "&SORT(UNIQUE(TOCOL(IF(TOCOL(SEARCH(TOCOL(IF(e=" ","",e)),a)),LEFT(TOCOL(COLUMN(A1:D1)&e))),2)))))))

=MAP(A2:A11,LAMBDA(x,LET(a,D1:G9,b,SEARCH(IF(a="",1,a),x),TEXTJOIN(", ",1,SORT(UNIQUE(TOCOL(IF(b>0,TAKE(a,1)),3)))))))

=MAP(A2:A11,LAMBDA(T,TEXTJOIN(", ",,FILTER(D1:G1,BYCOL(IFERROR(SEARCH(D2:G9,T),0),LAMBDA(A,MAX(A)))>1))))

=MAP(A2:A11,LAMBDA(z,LET(A,TEXTSPLIT(z," "),ARRAYTOTEXT(UNIQUE( SORT(TEXTSPLIT(TEXTJOIN(":",,MAP(A,LAMBDA(x,TEXTJOIN(":",,MAP(SEQUENCE(4),LAMBDA(y,IF(ISNUMBER(XMATCH(x,INDEX(D2:G9,,y))),"Group "&y,""))))))),,":"),,1))))))

=MAP(A2:A11, LAMBDA(y, LET(
a, PROPER(TEXTSPLIT(y,," ")), b, TOCOL(D2:G9,,1),
c, TOCOL(IFNA(D1:G1,ROW(D2:G9)),,1),
TEXTJOIN(", ",,SORT(UNIQUE(IF(ISNA(XMATCH(b,a)),"",c)))))))

=MAP(A2:A11, LAMBDA(a, TEXTJOIN(", ", 1, BYCOL(D1:G9, LAMBDA(c, IF(COUNT(SEARCH(TOCOL(c, 3), a)), TAKE(c,1),""))))))

=MAP(
 A2:A11,
 LAMBDA(x,
 TEXTJOIN(
 ", ",
 ,
 UNIQUE(
 SORT(
 TOCOL(
 REDUCE(
 "",
 TEXTSPLIT(x, , " "),
 LAMBDA(a, v, VSTACK(a, FILTER(D1:G1, BYCOL(D2:G9 = v, LAMBDA(a, OR(a))), "")))
 ),
 3
 )
 )
 )
 )
 )
)

=LET(header,D1:G1,data,D2:G9,d,TOCOL(data,1,1),h,TOCOL(REPT(header,1/(data<>"")),2,1),MAP(A2:A11,LAMBDA(x,TEXTJOIN(", ",1,UNIQUE(XLOOKUP(TEXTSPLIT(x," "),d,h,""),1)))))

=LET(a,A2:A11,
b,TEXTJOIN("||",TRUE,a),
C,TEXTSPLIT(b," ","\",,,""),
d,MAP(c,LAMBDA(x,TEXTJOIN(", ",TRUE,FILTER(D1:G1,
BY COL(D2:G9-FILTER(x,x<>""),
LAMBDA(y,OR(y))),"")))),
e,BYROW(d,LAMBDA(z,
TEXTJOIN(", ",TRUE,UNIQUE(
TOROW(UNIQUE(z),3))))),
f,IFNA(REDUCE(,e,LAMBDA(acc,itr, VSTACK(acc, TEXTSPLIT(itr,",")))),""),
g,BYROW(f,LAMBDA(y,
TEXTJOIN(", ",TRUE,SORT(UNIQUE(FILTER(y,y<>""),TRUE),,,TRUE)))),g)

=LET(tabl1,A2:A11, tabl2,D2:G9,head2,D1:G1,
group,MAP(
TOCOL(head2&"-"&tabl2),LAMBDA(a,TEXTBEFORE(a,"-"))
),
text,TOCOL(LOWER(tabl2)),
MAP(tabl1,LAMBDA(a,TEXTJOIN(", ",,
SORT(UNIQUE(FILTER(group,
MAP(text,LAMBDA(b,OR(TEXTSPLIT(a," ")=b))))))))))

=MAP(A2:A11,LAMBDA(t,JOIN(", ",FILTER(D1:G1,REGEXMATCH(t,"(?i)"&BYCOL(D2:G9,LAMBDA(c,TEXTJOIN("|",1,c))))))))

=MAP(A2:A11,LAMBDA(l,TEXTJOIN(", ",,SORT(UNIQUE(IF(ISNA(XMATCH(TOCOL(D2:G9,,8),TEXTSPLIT(l,," "))),"",INDEX(D1:G1,SEQUENCE(32,,,1/8))))))))

=LET(g,D2:G9,r,ROWS(g),c,COLUMNS(g),s,SEQUENCE(r*c,,,1/r),MAP(A2:A11,LAMBDA(l,TEXTJOIN(", ",,SORT(UNIQUE(IF(ISNA(XMATCH(INDEX(g,1+MOD(SEQUENCE(r*c,,0),r),s),TEXTSPLIT(l,," "))),"",INDEX(D1:G1,s))))))))

=MAP(A2:A11,LAMBDA(x,LET(g,D1:G1,w,D2:G9,p,(w<>"")+ISNUMBER(SEARCH(w,x))>1,TEXTJOIN(", ",,UNIQUE(TOCOL(IF(p,g,NA()),2,1))))))

=LET(g,D1:G9,_w,TOCOL(g,1,1),_c,SCAN("",_w,LAMBDA(a,x,IF(ISNUMBER(SEARCH("group",x)),x,a))),
r,MAP(A2:A11,LAMBDA(_t,TEXTJOIN(", ",,UNIQUE(FILTER(_c,ISNUMBER(SEARCH(_w,_t))))))),
r)

=MAP(A2:A11, LAMBDA(a,
 TEXTJOIN(", ",,
 UNIQUE(TOCOL(
 IF(SEARCH(" "&$E$2:$H$9&" ", " "&a&" "),$E$1:$H$1)
 ,2,1)))))

The IF / SEARCH line looks for each word from the second table in one sentence, and returns the corresponding group if there's a match (or an error if not).

TOCOL converts that to a column, drops the errors (with the second argument = 2), and returns the groups in order (scan by column with third argument = 1), UNIQUE removes duplicates, and TEXTJOIN combines them into one output.

And finally, MAP / LAMBDA applies those steps to each of the inputs.

If COUNTIF could take an array as the first argument, I could have done it a nicer way! : )

=LET(_tbl1,A2:A11,
_tbl2,D2:G9,
fn,LAMBDA(txt,ARRAYTOTEXT(FILTER(D1:G1,BYCOL(_tbl2,LAMBDA(x,SUM(--ISNUMBER(SEARCH(TOCOL(x,1),txt)))))))),
BYROW(_tbl1,LAMBDA(rv,fn(rv))))

(or)

=BYROW(A2:A11,LAMBDA(rv,ARRAYTOTEXT(FILTER(D1:G1,BYCOL(D2:G9,LAMBDA(x,SUM(--ISNUMBER(SEARCH(TOCOL(x,1),rv)))))))))

=MAP(A2:A11,LAMBDA(x,LET(a,D2:G9,ARRAYTOTEXT(UNIQUE(TOCOL(IF(SEARCH(IF(a<>"",a,0),x),D1:G1),2,1))))))

=MAP(A2:A11,LAMBDA(t,JOIN(", ",FILTER(D1:G1,REGEXMATCH(t,"(?i)"&BYCOL(D2:G9,LAMBDA(c,TEXTJOIN("|",1,c))))))))

=LET(rng,$D$2:$G$9,BYROW(A2:A11,LAMBDA(r,ARRAYTOTEXT(UNIQUE(SORT(TOROW(IF(rng="",NA(),IF(ISNUMBER(SEARCH(rng,r)),$D$1:$G$1,NA())),2),,,TRUE),TRUE)))))