google sheets - Reference a cell using formula?

Question

In Google Sheets I need to reference a cell by (column, row) in a way where I can replace "row" with a formula to find a specific row. In other words I can't use a reference like "A7" because my row is determined by a formula.

Here is the formula that gives me my row number:

=ArrayFormula(MAX(FILTER(ROW(B:B);NOT(ISBLANK(B:B)))))

question from:https://stackoverflow.com/questions/20822513/reference-a-cell-using-formula

Answer 1

You can use indirect() to dynamically reference cells

Use: indirect("string that evaluates to a reference")

example: =Indirect("A"&B2) copies the value of the cell with the row in B2 in column A

In your case, it would be (replace [column] with the desired column):

indirect("[column]"&ArrayFormula(MAX(FILTER(ROW(B:B);NOT(ISBLANK(B:B))))))