Reference a cell using formula?

43
votes

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)))))
1
google-sheetsreference
Here is a simpler function, "=COUNTA(B:B)" the result of which will be a number which represents a row. I have predetermined that I will need to reference/return the value of the cell in Column "O" which corrasponds to that row number. "=O,COUNTA(B:B)" how do I do this? – user3143232
Take a look at the 'INDEX' function[ [ref]](support.google.com/drive/answer/3098242) – Chris

1 Answers

77
votes

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))))))