Excel INDIRECT Errors: Causes, Fixes, and Examples

An Excel INDIRECT error usually means the text supplied to INDIRECT cannot be turned into a valid cell or range reference; the most common result is #REF!. Check the reference text for a typo, confirm any sheet or workbook it names exists, and make sure the reference uses the address style Excel expects.

What INDIRECT does

INDIRECT takes text and treats it as a reference. This is useful when a formula needs to build a cell address from a value in another cell, or select a worksheet based on a name entered by someone. Unlike a direct reference such as B3, the address passed to INDIRECT is interpreted from text when the formula calculates.

With the text B3 in A2 and the value 42 in B3 on the same worksheet, enter this formula in C2:

=INDIRECT(A2)

The formula returns 42 because A2 contains a valid reference to B3. If A2 instead contains B30, the formula returns the value in B30, or zero if that cell is empty. If A2 contains text that is not a valid reference, such as banana, INDIRECT returns an error rather than treating it as ordinary text.

The function has two arguments: the reference text and an optional setting for the address style. Excel uses A1-style references by default, such as B3 or A1:C10. The optional setting can be TRUE or omitted for A1 style, or FALSE for R1C1 style. INDIRECT does not turn a value like a worksheet name into a reference by itself; the text must describe a complete reference that Excel can interpret.

How to diagnose the error

Start by examining the exact text being passed to INDIRECT. If the formula uses a cell such as A2, click that cell and inspect its contents, including spaces and punctuation. If the reference is assembled from several cells or text fragments, temporarily put the assembled text in a spare cell. This makes invisible spaces, misspelled sheet names, and missing punctuation easier to spot.

Then check the reference in the formula bar or evaluate the parts separately. Confirm that the named cell or range exists, that any worksheet name is spelled exactly as it appears on its tab, and that an external workbook is available if the text points outside the current file. A reference that looks nearly right can still fail: Sheet 1 and Sheet1 are different names, and a sheet name with spaces needs single quotation marks around it in a reference.

Check whether the reference text is blank or contains leading or trailing spaces.
Confirm the address style: A1 references need the default or TRUE setting; R1C1 references need FALSE.
Check all worksheet, workbook, range, and defined-name spellings.
Test the formula with a simple known reference before rebuilding the full dynamic formula.

For a quick test, replace the cell-based text temporarily with a reference you know exists. If that works, the function itself is probably fine and the problem is in the text being supplied. Restore the original formula after checking the components.

What the different errors usually mean

#REF! is the signature error readers most often mean by an INDIRECT error. It usually indicates that the text does not describe a valid reference. The address may be mistyped, the worksheet or defined name may not exist, or the text may use R1C1 notation while the formula is set to A1 notation. A reference to a closed external workbook can also produce #REF! with INDIRECT; the source workbook generally needs to be open.

#NAME? commonly points to a problem elsewhere in the formula, such as a misspelled function name or an unquoted text value that Excel interprets as a name. It is distinct from a valid INDIRECT call whose reference text is invalid. Check the formula syntax and make sure literal text is enclosed in quotation marks.

#VALUE! may come from another part of a longer formula, or from a value or argument that is not appropriate for the calculation. Do not assume every error near INDIRECT has the same cause. If the formula is wrapped in another function, test the INDIRECT portion on its own and then add the surrounding calculation back one piece at a time.

A circular reference is another possible symptom of a badly constructed dynamic address. If INDIRECT points to the cell containing the formula, directly or through other formulas, Excel may warn about a circular reference. Trace the final address back to the formula cell rather than simply changing the error display.

Common causes and how to prevent them

Sheet names are a frequent source of failure. A sheet name containing spaces, punctuation, or certain special characters should be enclosed in single quotation marks within the reference. For example, if A2 contains East Data and cell B3 on that worksheet contains 125, use this formula in C2:

=INDIRECT("'"&A2&"'!B3")

It returns 125, provided a worksheet named East Data exists and its B3 cell contains 125. If A2 contains a different sheet name, or if that sheet is renamed or deleted, the generated reference will no longer work. The single quotation marks belong around the sheet name inside the reference text; they are not a substitute for checking the name.

Address style is another common issue. With 20 in B3, this formula entered in C2 returns 20:

=INDIRECT("R3C2",FALSE)

The FALSE argument tells Excel to read R3C2 as row 3, column 2, which is B3. If you omit FALSE, Excel interprets the text using A1 style, where R3C2 is not the intended address.

INDIRECT also relies on text, not on Excel tracking the relationship as it would for a direct cell reference. If text in A2 says B3 and rows are inserted, that stored text may keep pointing to B3 rather than adjusting to the cell that originally held the data. This makes INDIRECT more vulnerable to structural changes than ordinary references. Use it only when the dynamic text-based behavior is useful.

Ways to fix or safely handle a bad reference

Fix the source of the bad text whenever possible: correct the address, remove stray spaces, repair the sheet name, or choose the correct reference style. If a blank input should result in a blank output rather than an error, add a check before INDIRECT. With the reference text in A2 and the formula in B2, use:

=IF(A2="","",INDIRECT(A2))

This returns a blank when A2 is empty; if A2 contains B3, it returns the value in B3. It does not repair an invalid nonblank reference, so a typo in A2 can still produce #REF!.

For a user-facing message, IFERROR can replace an error result. With a reference text in A2 and the formula in B2, use:

=IFERROR(INDIRECT(A2),"Check reference")

If A2 contains a valid reference to a cell holding 42, the result is 42; if it contains an invalid reference, the result is Check reference. This is useful in a report, but it can hide the underlying problem. During troubleshooting, remove IFERROR so the original error remains visible. Avoid using a blank replacement everywhere: an apparently empty result can conceal a broken reference that should be fixed.

When INDIRECT is useful—and when to choose another method

INDIRECT can be appropriate for a workbook where a user selects a worksheet or range by entering its name, and the formula must follow that selection. It can also help with certain layouts where a reference is deliberately assembled from text. The trade-off is that the formula is harder to audit: Excel cannot readily follow a text string as a normal reference when the workbook is edited.

When the goal is simply to look up a value by a key, a lookup function is often clearer. With product IDs in A2:A20, prices in B2:B20, and the ID to find in D2, this formula returns the matching price or Not found:

=XLOOKUP(D2,A2:A20,B2:B20,"Not found")

XLOOKUP is available in Microsoft 365 and Excel 2021 or later. For older Excel versions, a combination such as INDEX and MATCH may be a compatible alternative. If a direct reference is all that is needed, use the direct cell or range reference rather than wrapping its address in text and calling INDIRECT.

INDIRECT is also volatile: Excel recalculates it when calculation occurs, even when its apparent inputs have not changed. A few instances may be inconsequential, but many formulas in a large workbook can add calculation overhead. If a file becomes slow, search for widespread INDIRECT use and consider whether direct references, lookup formulas, or a redesigned table would be easier to maintain.

Frequently Asked Questions

Why does INDIRECT return #REF!?
Usually, Excel cannot interpret the supplied text as an existing reference. Check the cell address, sheet name, quotation marks, reference style, and whether an external workbook is open.

Does INDIRECT work with a worksheet name stored in a cell?
Yes. Combine the name with the rest of the address as text, and include single quotation marks around the sheet name when needed, especially when it contains spaces. The worksheet must exist and the resulting address must be valid.

Can INDIRECT refer to a closed workbook?
INDIRECT generally returns #REF! when its text points to an external workbook that is not open. If a formula must retrieve data while the source file is closed, use a different approach, such as a direct external link or an appropriate data-import workflow.

How do I use INDIRECT with R1C1 references?
Supply FALSE as its second argument. For instance, the R3C2 text refers to B3 in R1C1 notation. Without FALSE, Excel expects A1-style text.

Is IFERROR a real fix for an INDIRECT error?
No. IFERROR changes what the worksheet displays when an error occurs; it does not make an invalid reference valid. Use it for a helpful presentation only after deciding how errors should be handled, and correct the reference text when possible.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.