Search code examples
exceljoinfilterexcel-formula

MS Excel - Join two structured tables

Why does this formula (which works) display BOTH structured tables?

From Stackoverflow, Chris Neilsen, August 15, 2021:

=LET(x, table1, y, table2,
rows1, ROWS(x), rows2, ROWS(y),
myrows, SEQUENCE(rows1+rows2),
mycols, SEQUENCE(1,COLUMNS(x)),
IF(myrows<=rows1, x, INDEX(y, myrows-rows1, mycols)))

It is the calculation step in LET():

IF(myrows<=rows1, x, INDEX(y, myrows-rows1, mycols))

that perplexes me. It appears that EITHER x OR y should result, NOT BOTH. Thanks.

I carefully worked through the formula and tested parts of the formula independently.

Solution

  • Figuring Out How a Formula 'Ticks'

    • Chris Neilsen's answer was posted before the release of the VSTACK function i.e. it can be simplified with:

      =VSTACK(Table1,Table2)

    • You could rewrite the formula e.g. in the following way:

    =LET(x, Table1, y, Table2,
rows1, ROWS(x), rows2, ROWS(y),
myrows, SEQUENCE(rows1+rows2),
myrows1, myrows<=rows1,
myrows2, myrows-rows1,
mycols, SEQUENCE(1,COLUMNS(x)),
result, IF(myrows1, x, INDEX(y, myrows2, mycols)),
result)
    • Now play with it by replacing the last occurrence of result with any of the other variables.
    • You could also return each of the variables (expressions), or just the unclear ones, in a standalone formula, e.g.:

    enter image description here