How do I get the data in a cell of the last column of a row?

Question

I'm using Google Sheets. Every week I add a column with new data, and I need the data from the latest column copied into another sheet. The formula needs to pull the cell of the last column in the row.

I've googled left and right and tried and tried.

The "best" formula that I have tried is:

=index(2:2,counta(2:2))

But I get:

error: Reference out of range

I've tried =index(2:2, 5), as a test, which should bring the data from row 2 column 5, no luck.

Answer 1

Provided that last column is populated, then something like:

=HLOOKUP(1E+100,B1:Z1,1)  

assuming a row starts in ColumnB and ends in ColumnZ.

Answer 2

I figured out the issue.

From Google:

Sample Usage

INDEX(A1:C20, 5, 1)

Syntax

INDEX(reference, [row], [column])

reference - The array of cells to be offset into.

row - [OPTIONAL - 1 by default] - The number of offset rows.

column - [OPTIONAL - 0 by default] - The number of offset columns.

I needed to offset by column, not by row. So my original formula

=index(2:2,counta(2:2))

counta(2:2) is supposed to bring me the row # based on the 'counta' of row 2:2, ie. the last row. But I actually need it to bring me the column.

This is the working formula:

=index(2:2,0,counta(2:2))