How to Refer to a Range by Name with INDIRECT in Excel

Turn text like "C2:C7" or a region name into a live reference with INDIRECT, so one input cell switches the data.

You will learn to

• Read a cell whose address is typed as text
• Sum a range whose address is stored in a cell
• Look up a range address by name and sum it

How it works

INDIRECT(ref_text) converts text into a real reference. If K1 contains the text D4, =INDIRECT(K1) returns whatever is in D4, and =SUM(INDIRECT("C2:C7")) sums that range. This lets a single input cell decide which data a formula uses.

A common pattern keeps a small directory of names and addresses (North → B2:B7) and looks up the address before passing it to INDIRECT. In Excel, INDIRECT also understands defined names, so =SUM(INDIRECT("North")) works when a range is named North. INDIRECT is volatile and does not adjust when rows are inserted, so keep its text addresses up to date.

=SUM(INDIRECT(VLOOKUP(K3,G2:H5,2,FALSE)))