INDIRECT for dynamic references in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of this INDIRECT formula?

Given the following setup:

Cell A1 contains the text B2.
Cell B2 contains the number 150.

What value will the formula =INDIRECT(A1) return?

A 150

B B2

C #REF! error

D 0

💡 Hint

INDIRECT uses the text in a cell to get the value from that referenced cell.

The formula INDIRECT(A1) reads the text in A1, which is 'B2', and returns the value in cell B2, which is 150.

❓ Function Choice

intermediate

Which formula correctly uses INDIRECT to sum a range defined by text in cells?

Cells A1 and A2 contain the text C1 and C5 respectively.

You want to sum the range from C1 to C5 using INDIRECT.

Which formula will do this correctly?

A =SUM(INDIRECT(CONCATENATE(A1, ":", A2)))

B =SUM(INDIRECT(A1 + ":" + A2))

C =SUM(INDIRECT(A1 & ":" & A2))

D =SUM(INDIRECT(A1 & "," & A2))

💡 Hint

Use & to join text strings in Excel formulas.

Option C correctly concatenates the text in A1 and A2 with a colon between to form a valid range reference like 'C1:C5'.

📊 Formula Result

advanced

What is the result of INDIRECT with R1C1 notation?

Cell A1 contains the number 3.

What value will the formula =INDIRECT("R" & A1 & "C2", FALSE) return if cell B3 contains 200?

A 200

B #REF! error

C R3C2

D FALSE

💡 Hint

The second argument FALSE tells INDIRECT to use R1C1 style referencing.

The formula builds the reference 'R3C2' which points to row 3, column 2 (cell B3). The value in B3 is 200.

🎯 Scenario

advanced

Using INDIRECT to reference a sheet name from a cell

You have two sheets named January and February.
Cell A1 in the current sheet contains the text February.
Cell B2 in the February sheet contains the value 500.

Which formula will correctly return the value 500 using INDIRECT?

A =INDIRECT(A1 & "!B2")

B =INDIRECT("B2" & A1)

C =INDIRECT(A1 & "B2")

D =INDIRECT("'" & A1 & "'!B2")

💡 Hint

Sheet names with spaces or special characters need single quotes around them.

Option D correctly adds single quotes around the sheet name and appends '!B2' to reference cell B2 in the February sheet.

❓ data_analysis

expert

How many cells will this INDIRECT formula reference?

Cell A1 contains the text D3.
Cell A2 contains the text D7.

What is the number of cells included in the range referenced by the formula =INDIRECT(A1 & ":" & A2)?

A 6

B 5

C 4

D 7

💡 Hint

Count all cells from D3 down to D7 inclusive.

The range D3:D7 includes cells D3, D4, D5, D6, and D7, which is 5 cells total.