How to use INDIRECT

The INDIRECT function displays the selected cell address as a text string.

=INDIRECT(ref_text,[a1])

[ref_text] = Select cell address to reference.
[a1] = Not obligatory. Identify the reference format to use function for [ref_text].

「TRUE, or nothing written」・・・Reference format in A1.
「FALSE」・・・Reference format R1C1.

Definition of function A function to display the selected cell address as a text string.
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

What is a reference format?

In Excel, there are 2 ways to reference a cell.
The one used most commonly is the ‘A1 format‘, and we identify the cell to format with the column alphabet starting with A and the row number that begins with 1.
Other than that there is the ‘R1C1 format‘ where we identify with a numerical value the distance between the cell which we are typing the formula in and the cell to reference.

For example, if we write a formula in cell C2 referencing cell A1, the format for the 2 will be different as follows:

A1 format・・・「=A1
R1C1 format・・・「=R[-1]C[-2]

In the R1C1 format, the reference cell A1 is 1 row up and 2 columns on the left from cell C2. (※’R’→Row, ‘C’→Column)

Usually the A1 format is used but we can easily change the format to R1C1 by changing the settings in Excel.

 

Example of use) INDIRECT

The table below shows the scores of the end of year exams in a school. We will use this table to explain how to use the INDIRECT function.

Normally, when referencing a cell we input a formula like ‘=A1‘ , but for the INDIRECT function we can display the referenced cell as a text string, using ” (double quotations).

① Extract from table the ‘name of the student sitting in seat no. 5’. 

[ref_text] : Surround cell to reference with ” (double quotations). → “C8”
=INDIRECT(“C8”)

 

With only this example the feature of this function might still be confusing, so let us look at another example.

② Extract from table the ‘name of the student sitting in seat no. 5’.

Search the row number of seat no. 5 using the MATCH function.

MATCH(5,B4:B8,0)

The reference cell range [B4:B8] begins from the 4th row, so we input ‘+3’ (counting from the original Excel sheet).

MATCH(5,B4:B8,0)+3

The result of the MATCH function ‘5’ and ‘+3’ adds up to ‘8’.
We use this value with the INDIRECT function.

[ref_text] : Surround cell ‘C’ with ” (double quotations) and complete the formula by using symbols to connect it with the MATCH function.
=INDIRECT(“C”&MATCH(5,B4:B8,0)+3)

 

Therefore, if we input for example ‘=A1’, we can only refer to ‘=A1’, but if we use the INDIRECT function we can use the MATCH and VLOOKUP together and create different types of formulas.

Refer to a cell from a different work sheet

The method in referring to cells from different work sheets with the INDIRECT function is a little different from the normal way, where we input something like ‘=New York!C7’ (cell C7 from the New York work sheet).

 

Example of use) Refer to a cell from a different work sheet

Below is a sales report of a chain store.
4 work sheets are created: ‘Sales amount total for 3 months’, ‘New York’, ‘California’, and ‘Texas’. We plan to display the ‘sales amount total for 3 months’ for each area. (※All of the tables are created in the same cell location for ‘New York’, ‘California’ and ‘Texas’)

We will use the INDIRECT function to extract the sales amount total for each month and area.

Sales amount total (3 months) sheet

 

‘New York’, ‘California’, ‘Texas’ work sheet

 

After using the INDIRECT function)

① Extract the sales amount total for April from the New York sheet.

[ref_text] : Input ‘!’ that signifies sheet and cell C7 where the sales amount total for April is calculated. Surround everything with ” (double quotations). → “!C7”

We do not manually type the sheet name, but use cell C3 as an agent. Therefore we type ‘C3’ before the [ref_text].
=INDIRECT(C3&”!C7″)

The above means ‘New York (C3)!C7’ = Cell C7 from the New York sheet, so that is how Excel extracts the sales amount total of April.
The strength of this method is that cell C7 is a ‘text string‘. Therefore, the selected cell C7 will not change whether Autofilled or copied.

 

The results for Autofill)

Only the agent (to select the correct sheet) is Autofilled through relative cell reference.

 

Can we refer to values from a different book (file)?

There are restrictions, but we can also refer to values from different books with the INDIRECT function.

 

Example of use) Refer to a cell from a different Excel book

To refer to a different Excel book, surround “Book name to refer+Excel extension (such as .xlsx)” with “[ ]”, and when locating the place of book, surround it with ‘ (single quotations).

→ Example) [Meeting information.xlsx], ‘C:\Temp\[Meeting information.xlsx]’

What is an extension?

When saving a file in Windows, there is always some kind of text that comes after the file name. This is called a file extension.

A file extension is there to distinguish file types and is different according to application. For example, in Excel the following extensions will be added automatically at the end of the book name:

Books from Excel 97 – 2003 → .xls
Books from Excel 2007  → .xlsx
Books from Excel 2007 → .xlsm

 

 

Here we have decided to separate the ‘New York’, ‘California’ and ‘Texas’ sheet to a different book and named it ‘April-June total’. In this case, the formula in the INDIRECT function in the ‘sales amount total (3 months)’ sheet  will be the following:

① Extract April’s sales amount total of New York from a different book, ‘April-June total’.

(1) Add the extension ‘.xlsx’ after the file name ‘April-June total’ and surround it with ‘[ ]’. Surround the whole thing with ” (double quotations).

“[April-June total.xlsx]”

(2) Select cell C3 as the agent like before and connect it with the book name using ‘&’.

“[April-June total.xlsx]”&C3

(3) Surround everything with ‘ (single quotations), and surround everything with ” (double quotations) to make the single quotation itself a part of the text string.

” ‘ “& “[April-June.xlsx]”&C3 &” ‘ “

The use of ‘ (singly quotations) is complicated, but finally the above formula signifies the ‘New York (C3) sheet from the April-June total book‘.
The ‘!’ symbol is not there yet so we will add it in step (4).

(4) Input ‘!’ which signifies sheet, and surround cell with April’s sales amount total for New York, ‘C7’ with ” (double quotations).

” ‘ “& “[April-June.xlsx]”&C3 &” ‘ ” &“!C7”

(5) Finally input this in the INDIRECT function to complete.

=INDIRECT(” ‘ “& “[April-June.xlsx]”&C3 &” ‘ ” &”!C7″)

 

POINT!

At the beginning of this section we have explained that there are ‘restrictions‘ to when referring to a different book.

This means that when we want to refer to values from a different book, there are some functions in which we must have the corresponding book open in advance.
The INDIRECT function has that restriction, so if the book ‘April-June total.xlsx’ is not open, ‘#VALUE (error)’ will appear.

 

Create a refined list using data validation

When there are many tables in Excel, we might want Excel to ‘automatically display a certain list (created in advance) in section ××× within the group ○○○‘, in other words, a narrowed-down list.
If this is possible, it will be much easier to manage large tables. To carry this out, we use the INDIRECT function, data validation, and defining names.

→ For more information on data validation and defining names, click here

 

Example of use) Create a refined data validation list

The table below is created to select pitchers from each baseball team that are suitable for the national game.

In the left table, we select each of the strengths ‘Starter’ ‘Middle’ ‘Closer’ and then the team name and name of pitcher further on.
The list on the right shows the name, strengths and team name of the candidates.

When we select the [Strength] and [Team], we would like the name of candidate to appear automatically (in column D).

 

(1) Create a data validation list in the [strength] cell.

① Highlight cell range of ‘Strength’, [B5:B9].
② Click the ‘Data’ tab.
③ Click  from the ribbon and then ‘Data Validation’.

 

(2) When the dialog box appears, do the following:

① Click ‘List’ from [Allow:].
② Type ‘Starter,Middle,Closer’ in [Source:].
③ Click [OK] to complete.

 

(3) Define the name of the team names as follows:

① Highlight cell range with team name, [F5:H5].
② Type ‘Starter’ in the [name box] on the left hand side of Excel.

 

(4) Do the same as (3) with ‘Middle’ and ‘Closer’.

 

(5) Set up data validation so that the team name will be listed automatically.

① Highlight the cell range of ‘Team’, [C5:C9].
② Select ‘List’ from [Allow:].
※See step (1) to see how to open the ‘Data Validation’ dialog box.
③ Input the following formula in [Source:] and click [OK].

=INDIRECT($B5)

If ‘Starter’ is selected in the B column, the group of the teams named ‘Starter’ will be displayed as a list.

 

(6) Follow the steps below to define the name of the candidate names.

① Highlight cell range of ‘ANDKEES’ ‘Starter(s)’, [F6:F7].
② Type ‘StarterANDKEES’ in the [name box].

 

(7) Do the same as (6) with others by ‘team name for each strength‘. Below is an example. (※There will be 9 times of naming)

 

(8) Set up data validation so that the candidate name will be listed automatically.

① Highlight the cell range of ‘Name’, [D5:D9].
② Select ‘List’ from [Allow:].
③ Input the following formula in [Source:] and click [OK].

=INDIRECT(TEXT($B6&$C6,”@”))

※Notice that we are connecting cell B6 (Strength) and cell C6 (Team) with TEXT function.

If ‘Starter’ is selected in the B column and if ‘ANDKEES’ is selected in the C column, the group of candidates named ‘StarterANDKEES’ will be displayed as a list.

 

(9) Data validation is now complete.

Now ‘Strength (column B)’ and ‘Team (column C)’ can be selected with a drop-down list, and once those 2 are selected, ‘the candidate name (column D)’ that applies will appear automatically as a list.