How to make a VLOOKUP in Table 2 always point to the last column in a growing Table 1?

While you could do it with VLOOKUP by using the COLUMNS function to figure out the last column, the problem you'll run into is when you add a new rightmost column to Table 1 the range in the VLOOKUP will not automatically expand to include it. It will throw an error trying to access a column outside of the specified range.

You can use this instead:

=OFFSET(Table 1::$A$1,XMATCH($A2,Table 1::A,0),COLUMNS(Table 1::$1:$1)−1)

You're right. I was working on the premise that the names would be fixed, but that's not necessarily going to be true.

Fortunately, it's easy to extend my model to capture the names, too - either a separate formula in Table 2::A1 that grabs all the content from Table 1::A (thus grabbing the names in the same order), or even better, ask CHOOSECOLS() for two columns - the first and the last.

The only tricky part here is having to dynamically calculate the size of the table. Fortunately, LET() makes that easier.

So, in a new, blank Table 2, simply set cell A1 to be:

=LET(num_row,ROWS(Table 1::$A,0),

    num_col,COLUMNS(Table 1::$1:$1,0),

    CHOOSECOLS(INDIRECT("Table 1::R1C1:R"& num_row&"C"&num_col,FALSE),1,−1)
)

This creates two variables, num_row and num_col which calculate the number of rows and columns, respectively, in Table 1.

These are then used in an INDIRECT() formula that builds a dynamic reference to the entire Table 1, based on the number of rows and columns.

This is then used in CHOOSECOLS() to select the first and last columns, and your table is complete.

It would be nice if you could just reference the entire table in CHOOSECOLS(), without having to jump through the hoops of working out how many rows and columns there are, but the workaround isn't too hard.

Oh, this one's easy... CHOOSECOLS()

Given an array (e.g. all the rows in Table 1), you can use CHOOSECOL()s to select a column. Negative numbers are taken from the end of the columns, so in Table 2::B1 set:

=CHOOSECOLS(Table 1:2:7,-1)

Love the new array formulas. That is definitely the simplest solution if the people's names are in the same order in both tables (like they are in the screenshot). Only problem I see is that new names added to Table 1 will not get picked up by the formula, even when new rows are added to Table 2. Being a formula that "spills", though, it requires only the one formula to be edited (and new rows added to Table 2) to include new names.

A colon is missing in the posted formula

=CHOOSECOLS(Table 1::2:7,-1)

There are several different formulas vailable to get the names from Table 1 to Table 2 to ensure they are in the same order.

In retrospect, there's no need to use LET() here... since you're only using the value once, and it's just a simple calculation, the same result can be built in the single function:

=CHOOSECOLS(INDIRECT("Table 1::R1C1:R"& ROWS(Table 1::$A,0)&"C"&COLUMNS(Table 1::$1:$1,0),FALSE),1,−1)

I like LET() since it makes it easier to construct nested formulas and see each step separately, but there's no problem in incorporating the whole thing into one line.