Welcome to this tutorial that will show you how to get the value of a cell using the address of the cell (row and column) in Excel and Google Sheets.
Get the Cell Value with the INDEX function
We can get the value of a cell (its content) using the INDEX function.
The INDEX function searches for a cell within a specified range and returns its value.
=INDEX(A1:J10, C3, C4)
In the example above, the specified range in the INDEX formula is “A1:J10”, the row is cell “C3” (“3”), and the column is cell “C4” (“5”).
The formula scans through the range “A1:J10”, checks the cell in row 3 and column 5 (“E3”), and returns its value (“Success”).
Get the Cell Value with the INDIRECT function
Another option to get the value of a cell using its address is by using the INDIRECT function.
We can use the INDIRECT function in two different ways.
Using Textual Reference in the INDIRECT function
The INDIRECT function converts a specified text string into a cell reference and returns its value.
=INDIRECT("R" & 3 & "C" & 2, FALSE)
In the example above, the specified text string is “R3C2”. This means row 3, column 2. Therefore, the formula returns the value at that address (“Sarah”).
Note: The FALSE argument in this formula indicates the cell reference format (R1C1). (TRUE would use the A1 reference style).
Get the Cell Value using INDIRECT and ADDRESS
The second way to get the value of a cell using the INDIRECT function is by combining it with the ADDRESS function.
=INDIRECT(ADDRESS(4,2))
Let’s break down the formula into multiple steps:
=ADDRESS(4,2)
The ADDRESS function takes a specified row number (“4”) and column number (“2”) and returns its absolute reference (“$B$4”). Therefore, the absolute reference for the cell in column 2 (column B based on its position) and row 4 is $B$4.
The INDIRECT function returns the value of the referenced cell.
=INDIRECT(D3)
By combining these steps, we get our initial formula:
=INDIRECT(ADDRESS(4,2))
Note: The INDIRECT function is volatile. It recalculates every time the workbook does, which can slow down Excel. We recommend avoiding using a large number of INDIRECT functions in your workbooks.
Get the Cell Value by Address in Google Sheets
These formulas work exactly the same way in Google Sheets as they do in Excel.
To learn more about advanced features of Google Sheets, visit Crawlan.com.
