Let’s say we want to find the annual sales at a certain branch in a given year:
Specifically, we want to design a set of mini-search bars where we can type the row and column labels and then give us the value of a cell in their intersection. For example, we want to find the annual sales of the Fifth Avenue branch in 2016. The steps are as follows:
Step 1: Find the column number for the Fifth Avenue branch and the row number for the 2016 year.
Step 2: Convert the column and row numbers into a cell reference and then the value stored in it accessed.
In the previous tutorial, we learned how to do Step 1 using the MATCH function. In this tutorial, we will now learn how to do Step 2 using two new functions: ADDRESS and INDIRECT functions. These functions allow you to handle strings of cell references. The ADDRESS function can generate cell references from column and row numbers while the INDIRECT function can use the cell reference stored as a string to point to the cell.
The ADDRESS function is used to combine the row number and the column number (not a letter, unlike what is displayed in Google Sheets) into a cell reference. At its simplest, the ADDRESS function has the following syntax:
=ADDRESS(row_number, column_number)
Where the row_number and the column_number can either be specified as a number or point to a cell containing those numbers. The column_number cannot be a letter.
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 cell being referenced is fixed, but the formula that will use it has to be moved a few times. Sometimes, however, a relative reference is necessary. Relative references are useful when the references are of individual cells located in a regular array. In fact, you can even choose to fix either the row or column number while leaving the other one 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, ref_type)
Where the ref_type is where you can specify the type of reference. The values are shown below:
By default, the ADDRESS function generates a cell reference using the A1 notation, where the column is specified as a letter and the row is specified by a number. One example of a cell reference using such notation is F6, where F is the sixth column and 6 is the sixth row. You can also set the ADDRESS function to convert it to another notation, the R1C1 notation. F6 is then specified as R6C6 in the R1C1 notation. To consider changing the notation, use the following syntax of the ADDRESS function:
=ADDRESS(row_number, column_number, ref_type, A1_notation)
Where A1_notation can have either be TRUE or FALSE:
TRUE - A1 notation (F6)
FALSE - R1C1 notation (R6C6)
If you want to use the R1C1 notation, you can then set the ADDRESS function as
=ADDRESS(row_number, column_number, ref_type, FALSE)
Finally, the cell references we can make from the syntaxes that we have specified above will generate cell references that will point to the cells in the same sheet. What if you need to point to cells in a different sheet? Additional information can be added for the name of the sheet. The syntax then is:
=ADDRESS(row_number, column_number, ref_type, A1_notation,”sheet_name”)
Where the sheet_name is the name of the sheet where the cell is located. Note that sheet_name must be enclosed in quotation marks.
Below is a set of examples of the ADDRESS function that uses the different syntaxes listed in this section.
The INDIRECT function is used to display the content of a given cell reference stored in another cell. At its simplest, the function simply works as follows:
=INDIRECT(cell)
Where cell contains the cell reference that you want to use.
If you enclose the cell inside quotation marks, the INDIRECT function will display the content of that cell. However, you can already do this more simply with an equal sign followed by the cell reference itself.
If the cell reference is specified in R1C1 notation, then use the following syntax:
=INDIRECT(cell, A1_notation)
Then set the A1_notation to FALSE (by default, this is set to TRUE for A1 notation):
=INDIRECT(cell, FALSE)
Below is a set of examples of the INDIRECT function that uses the different syntaxes listed in this section.
We can combine ADDRESS and INDIRECT functions to form more powerful formulas for searching specific values in the sheet. As an example, we want to create a function that can be used to search for the annual sales of a branch in a specific year:
The years are specified in the first column as the headers of each row while the branches are specified in the first row as the headers of each column.
Let’s say we already know the column number and the row number that we are looking for. How can we use that information to find the value stored in the cell where specified column and row intersect? The formula simply consists of the ADDRESS function nested inside the INDIRECT function:
=INDIRECT(ADDRESS(column,row))
Where the column and row point to the cells containing the column number and row number, respectively. Note that the column and row numbers should correspond to their column and row numbers in the sheet, not to the column and row number in the array.
Here is the result:
We have now completed this two-part tutorial series on finding cell values given the column and row headers.
Click here to access the sample sheet used in writing this tutorial.
