INDIRECT function

INDIRECT returns the value or reference specified by a text string, letting you build dynamic cell or range references on the fly.

=INDIRECT(ref_text, [a1])

Generate a INDIRECT formula

Describe what you need. The generator will reach for INDIRECT where INDIRECT is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How INDIRECT reads its arguments
ref_textrequireda1optionalINDIRECT →
ArgumentRequiredDescription
ref_textRequiredA string (or reference to a cell containing a string) that represents a cell or range address; if the string is not a valid address, INDIRECT returns an error.
a1OptionalOptional logical flag; TRUE (or omitted) treats ref_text as A1-style notation, FALSE forces R1C1 notation, and any non-boolean value triggers a #VALUE! error.

Returns

It returns a cell value, a range, or an error, matching the shape of the referenced range.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Pull the most recent service cost for a specific vehicle

VehicleOdometerService DateCostGarage
Truck-12452002024-03-15320North Garage
Van-03381002024-02-10210East Garage
=INDIRECT("B" & MATCH("Truck-12",A:A,0))

Result: 320

MATCH finds the row where "Truck-12" appears in column A (row 2). The formula concatenates "B" with that row number, producing "B2", which INDIRECT then resolves to the Cost cell for that vehicle, returning 320.

2. Summarize total service cost for a dynamically chosen garage

VehicleOdometerService DateCostGarage
Truck-12452002024-03-15320North Garage
Van-03381002024-02-10210East Garage
Car-07215002024-01-22150North Garage
Truck-09600002024-04-01400South Garage
=SUM(INDIRECT("D" & ROW(INDIRECT("A2:A5"))))

Result: 880

ROW(INDIRECT("A2:A5")) returns the array {2;3;4;5}. Concatenating "D" creates the address array {"D2";"D3";"D4";"D5"}. INDIRECT turns those into references to the Cost column, and SUM adds the four values (320+210+150+400) to give 880.

3. Create a hyperlink to the service record of the vehicle with the highest odometer

VehicleOdometerService DateCostGarage
Truck-12452002024-03-15320North Garage
Van-03381002024-02-10210East Garage
Car-07215002024-01-22150North Garage
Truck-09600002024-04-01400South Garage
=HYPERLINK(INDIRECT("""#" & ADDRESS(MATCH(MAX(B:B),B:B,0),1) & "!A1"""), "View Record")

Result: View Record (clickable link to the sheet row containing Truck-09)

MAX(B:B) finds the highest odometer reading (60000). MATCH returns its row (5). ADDRESS builds "A5", the cell that contains the vehicle name. The concatenated string forms a sheet-internal reference like "#A5!A1", which INDIRECT feeds to HYPERLINK, producing a clickable label that jumps to the row for Truck-09.

Common errors

Which INDIRECT error are you seeing?
INDIRECT returned an error#REF!
Create the missing sheet or correct the sheet name in the text string.
#VALUE!
Shorten the reference string or build it in parts using CONCATENATE or TEXTJOIN.
#REF!
Replace the offending argument with TRUE/FALSE or omit it entirely.
ErrorWhy it happensHow to fix it
#REF!ref_text points to a sheet name that does not exist, e.g., INDIRECT("'MissingSheet'!A1").Create the missing sheet or correct the sheet name in the text string.
#VALUE!ref_text exceeds 255 characters, which INDIRECT cannot parse.Shorten the reference string or build it in parts using CONCATENATE or TEXTJOIN.
#REF!The a1 argument is supplied as a non-boolean value such as a number, causing INDIRECT to reject the parameter.Replace the offending argument with TRUE/FALSE or omit it entirely.

Tips and when to use something else

  • Use INDIRECT when you need a reference that changes based on user input, like a dropdown that selects a month sheet.
  • Remember that INDIRECT is volatile; it recalculates on every workbook change, which can slow large models.
  • If you only need to look up a value in a known column, consider INDEX/MATCH for better performance.
  • When referencing closed external workbooks, INDIRECT will return #REF!; use the more robust IMPORTRANGE (Google Sheets) or Power Query (Excel) instead.

Related functions

VLOOKUPVLOOKUP searches the first column of a table and returns a value from a specified column in the same row, optionally using exact or approximate matching.INDEXINDEX returns the value or array of values from a specified position within a given range, based on row and optional column numbers.MATCHMATCH returns the relative position of a lookup value within a one-dimensional range, optionally using exact, less-than, or greater-than matching.OFFSETOFFSET returns a reference to a range that is a specified number of rows and columns from a starting cell, optionally sized by height and width.ADDRESSADDRESS returns a text string that represents a cell reference based on supplied row, column and optional absolute/relative settings.

Frequently asked questions

How can I use INDIRECT to reference a different sheet based on a cell value?
Place the target sheet name in a cell, say B1, and build the reference with CONCATENATE: =INDIRECT("'" & B1 & "'!A1"). The function will then pull the value from cell A1 of the sheet named in B1.
Why does INDIRECT return #REF! when I reference a range that exists?
If the referenced range includes a column or row that has been deleted after the formula was written, Excel treats the address as invalid and returns #REF!. Updating the reference to the current range resolves the error.
Can INDIRECT be used with R1C1 notation?
Yes. Set the optional a1 argument to FALSE and supply an R1C1 style string, for example =INDIRECT("R2C3",FALSE) returns the value from cell C2.
Is there a way to make INDIRECT less volatile?
Indirect itself is always volatile, but you can wrap it in a LET function to calculate the reference once and reuse it, reducing the number of indirect calls and improving performance.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator