Hey there, my favorite spreadsheet enthusiasts! Today, we’re going to dive deep into the world of Google Sheets and explore the powerful functionalities of the ADDRESS and INDIRECT functions. These two functions will enable you to find and use cell references in your spreadsheets. Exciting, right? Let’s get started!
The ADDRESS function: Creating a Cell Reference
The ADDRESS function is used to combine the row and column numbers (not letters, as displayed in Google Sheets) into a cell reference. It’s pretty straightforward to use. Here’s the basic syntax of the ADDRESS function:
=ADDRESS(row_number, column_number)Both the row_number and column_number arguments can be either numbers or cell references containing those numbers. However, the column_number cannot be a letter.
But wait, there’s more! You can also specify the type of cell reference used. By default, the generated cell reference is an absolute reference. Absolute references are useful when the referenced cell is fixed, but the formula using it needs to be moved around. However, there are times when a relative reference is needed. Relative references are useful when the references correspond to individual cells in a regular table. In fact, you can even choose to lock either the row number or the column number while leaving the other as a relative reference. To specify the type of reference that will be generated, you add additional information to the function:
=ADDRESS(row_number, column_number, reference_type)The reference_type allows you to specify the type of reference. You can choose from the following values:
- By default, the ADDRESS function generates a cell reference using the A1 notation, where the column is specified by a letter and the row by a number. For example, F6 is the sixth column and the sixth row. You can also set the ADDRESS function to convert it into another notation, the R1C1 notation. F6 is then specified as R6C6 in R1C1 notation. To change the notation, use the following syntax of the ADDRESS function:
=ADDRESS(row_number, column_number, reference_type, A1_notation)The A1_notation can be either TRUE or FALSE:
- TRUE: A1 notation (F6)
- FALSE: R1C1 notation (R6C6)
If you want to use the R1C1 notation, you can set the ADDRESS function like this:
=ADDRESS(row_number, column_number, reference_type, FALSE)Finally, the cell references we can create from the specified syntaxes will generate cell references that point to cells within the same sheet. But what if you need to point to cells in a different sheet? Additional information can be added for the sheet name. The syntax then becomes:
=ADDRESS(row_number, column_number, reference_type, A1_notation, "sheet_name")The sheet_name is the name of the sheet where the cell is located. Note that the sheet_name must be placed between quotation marks.
Here’s a handy set of examples of the ADDRESS function using different syntaxes presented in this section:
The INDIRECT function: Displaying the Content of a Given Cell Reference
The INDIRECT function is used to display the content of a given cell reference, which is stored in another cell. The function works simply like this:
=INDIRECT(cell)The cell argument contains the cell reference you want to use.
If you wrap the cell in quotation marks, the INDIRECT function will display the content of that cell. However, you can achieve the same result more simply by using an equal sign followed by the cell reference itself.
If the cell reference is specified in R1C1 notation, use the following syntax:
=INDIRECT(cell, A1_notation)Then set the A1_notation to FALSE (it’s set to TRUE by default for A1 notation).
Here’s a nifty set of examples of the INDIRECT function using different syntaxes presented in this section:
Finding the Value of a Cell Using Column Header and Row Header
Now that we’ve seen how to use the ADDRESS and INDIRECT functions individually, let’s explore how we can combine them to create more powerful formulas for searching specific values in a spreadsheet. Let’s consider the following example:
We want to create a function that can be used to search for a branch’s annual sales for a specific year. Years are specified in the first column as headers of each row, while branches are specified in the first row as headers of each column.
Let’s say we already know the column number and the row number we’re looking for. How can we use this information to find the value stored in the cell where the specified column and row intersect? The formula simply involves nesting the ADDRESS function inside the INDIRECT function:
=INDIRECT(ADDRESS(column, row))The column and row arguments refer to the cells containing the column number and row number, respectively. Note that the column and row numbers should correspond to those in the sheet, not the numbers in the table.
Here’s the result:
And there you have it! You now know how to use the ADDRESS and INDIRECT functions in Google Sheets to find and use cell references. I hope this article has been helpful to you, and that you’re ready to utilize them in your own spreadsheets.
In the meantime, make sure to check out our website, Crawlan.com, for more tips and tutorials on Google Sheets and many other exciting subjects.
Until next time, happy spreadsheeting!
Click here to access the sample spreadsheet used in this article.
