In this article:

May 8, 2024

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.

Get Google Sheets productivity and automation tips delivered straight to your inbox

We'll email you 1-3 times a week — and never share your information.

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->