?: Use a dynamic array formula and set theory (Union / Not), across two tables to find all uniques, and entries in table2 not in table1

[chuckthenerd]

Solution sought for the following problem:

as mentioned in columns E and I, I'm trying to author a dynamic array formula against 2 excel tables: Table1 and Table2. The expected results are mentioned in G2:G5 and K2.

The closest solution I've found for:
cell E2 is

=UNIQUE(CHOOSE({1,2},Table1,Table2))

but that returns a 3 row 2 column aggregate table instead of a 4 row 1 column. The challenge I'm having is appending Table2 to Table1 as a 1 dimension column.

The following formula for cell I2 is close, but easily broken since it requires similar dimensions on the tables and that they align values left and right, so it's unacceptable.

=FILTER(Table2[Table2],Table2[Table2]<>Table1[Table1],"Tables match")

For the simple example above it would work, but with the real-world data I'm using it will rapidly break.

 

[DanteAmor]

Hi and welcome to MrExcel

Here an option with macro to consider

Sub UnionTables()
  Dim dic As Object, itm As Variant, i As Long
  Dim a As Variant, b As Variant, c As Variant
  
  Set dic = CreateObject("Scripting.Dictionary")
  a = Range("Table1").Value2
  b = Range("Table2").Value2
  ReDim c(1 To UBound(b))
  
  For Each itm In a
    dic(itm) = Empty
  Next
  For Each itm In b
    If Not dic.exists(itm) Then
      dic(itm) = Empty
      i = i + 1
      c(i) = itm
    End If
  Next
  Range("E2").Resize(dic.Count).Value = Application.Transpose(dic.keys)
  Range("I2").Resize(i).Value = Application.Transpose(c)
End Sub