It is very common in Excel to need to create a single table from the two or more other tables. For example, different users may edit different worksheets but you need to aggregate all the data for processing.

This can be done relatively simply using structured references to tables. I have my data in two tables “Table1″ and “Table2″ and I want the union in “Table3″. For convenience I create RowId column in Table3. To the right of this I enter,

= IF([RowId]<=ROWS(Table1)
    , INDEX(Table1[column1],[RowId])
    , INDEX(Table2[Column1],[RowId]-ROWS(Table1))
  )

The IF statement determine whether we should be rendering a value from Table1 or Table2. The INDEX function returns the data from the appropriate column and row.

In practice there is a small problem with this formula; when you drag it right to create more columns Excel treats the [RowID] as a relative reference and changes it. To make it absolute we use the INDIRECT function. This does not change the simple logic of the above formula but makes it easier to use.

=IF( INDIRECT("Table3[RowId]")<=ROWS(Table1)
    ,INDEX(Table1[column1],INDIRECT("Table3[RowId]"))
    ,INDEX(Table2[Column1],INDIRECT("Table3[RowId]")-ROWS(Table1)))