Can't use INDEX inside an ARRAYFORMULA with dynamically determined last row (VLOOKUP not working either)

Question

In this case: no easy way to specify the last column in a range when columns can be added to, while simultaneously allowing for an ever, automatically increasing row count.

The design is a Google spreadsheet+form, with subsequent tabs/sheets that do most the heavy lifting. The form will need to change over time, and those tabs doing the heavy lifting need to keep-up (got some code to do that), so more columns will get added, and I don't want the user to have to go-back and look for and modify formulas that need to be updated given the new last columns.

So, I add to the columns when a new spreadsheet element is added, and the last column contains the results I need. I just can't hard-code the position of that column in other formulas that use the results, plus, I need to handle it for an ever increasing number of rows.

Determining the last column dynamically was an easy fix to find, which just gets a list of all the column numbers, returning the greatest:

=max(filter(column(D1:1),len(D1:1)))

... where any column letter will do. So, for a given row, you can grab the last column's data:

    =index(E3:3,1,max(filter(column(E1:1),len(E1:1)))-4)  
// the "-4" is to offset for the 4th cell in the range.

But, INDEX doesn't work in an ARRAYFORMULA. Searching for help, there's a lot of replies that say to use VLOOKUP in these cases, including answers concerning a dynamic column number. Maybe I'm missing the point, but the corresponding VLOOKUP, AFAIK, is:

=VLOOKUP(E3,E3:3,max(filter(column(E1:1),len(E1:1)))-4)

Which, in an ARRAYFORMULA (to span down all rows) looks like:

=ARRAYFORMULA(VLOOKUP(E3:E,E3:3,max(filter(column(E1:1),len(E1:1)))-4))

... which doesn't work because the single row range, "E3:3" remains static for all the rows, so I just get the results for the 3rd row in every other rows results.

But, I found I could use:

=ARRAYFORMULA(E3:E & ": " & indirect("R3C" & B1 & ":C" & B1,FALSE))
// where B1 is max(filter(column(E1:1),len(E1:1)))

This concatenates the entry in the 5th column with ": " then with the entry in the last column. The range specifier "E3:E" in ARRAYFORMULA repeats for all rows after the third, and the R1C1 range notation in the INDIRECT of R3Cn:Cn (where n is the last column number) extends the ARRAYFORMULA down the last column.

Is there a better explanation of that behavior?

What was I doing wrong in the VLOOKUP? ...best I can tell, VLOOKUP is the way to do an INDEX in an ARRAYFORMULA, but don't see the solution there.

player0 player0 · Accepted Answer · 2019-03-16T21:21:11

to get the range of the last non-empty column based on header row (1st row) you need to construct it like this:

=INDIRECT(ADDRESS(1,MAX(FILTER(COLUMN(D1:1),LEN(D1:1))),4)&":"&
 SUBSTITUTE(ADDRESS(1,MAX(FILTER(COLUMN(D1:1),LEN(D1:1))),4),1,""))

then you can use it in VLOOKUP like this:

=ARRAYFORMULA(IFERROR(VLOOKUP(E3:E, 
 {Form!E3:E,INDIRECT(ADDRESS(3,MAX(FILTER(COLUMN(Form!D1:1),LEN(Form!D1:1))),4)&":"&
  SUBSTITUTE(ADDRESS(1,MAX(FILTER(COLUMN(Form!D1:1),LEN(Form!D1:1))),4),1,""))}, 2, 0), ))

which will return you the values of last column if there will be an match

Can't use INDEX inside an ARRAYFORMULA with dynamically determined last row (VLOOKUP not working either)

1 Answers