Formula Errors

How to Fix Excel INDIRECT #VALUE! Error with Spilled References

Algirdas Jasaitis Oct 10, 2026 869 views

Question details

The user needs to construct dynamic cell ranges using spilled references (F2#, G2#) generated by functions like FILTER, DROP, and ADDRESS, but the INDIRECT function returns an error.

How to Fix Excel INDIRECT #VALUE! Error with Spilled References

Product: Excel
Device & OS: not provided
Scenario: Dynamically defining a cell range for further calculation by feeding spilled array references into the INDIRECT function.
Observed behavior: The INDIRECT function returns a #VALUE! error when referencing spilled arrays (e.g., F2#), though it works correctly when referencing individual single cells.

Before you start

Review your dataset structure to ensure you understand the exact layout of the source data, as moving away from INDIRECT will require targeting the raw data array directly using functions like INDEX.

Solution 1Recommended

Use INDEX and SEQUENCE Instead of INDIRECT

Replace the volatile INDIRECT function with INDEX and SEQUENCE to handle dynamic array outputs natively without triggering a #VALUE! error.

The INDIRECT function is not programmed to interpret spilled array identifiers (like F2#) as paired single-cell references to construct a range. Instead of forcing INDIRECT to read an array, you can use the INDEX function combined with SEQUENCE to extract the exact dynamic range from your source data.

Identify your source data array

Locate the original raw data range that your spilled arrays (FILTER, DROP, etc.) are pulling from. You will use this as the primary array for your INDEX function.

Construct the SEQUENCE function

Use the SEQUENCE function alongside ROWS or COLUMNS to dynamically generate the series of row or column numbers that correspond to the size of your spilled array.

Nest within INDEX

Wrap the SEQUENCE formula inside the INDEX function. For example, instead of INDIRECT(F2#&":"&G2#), write a formula like =INDEX(SourceData, SEQUENCE(ROWS(F2#)), 1) to dynamically return the required values.

Use INDEX and SEQUENCE Instead of INDIRECT

Performance Improvement: Unlike INDIRECT, which is a volatile function that recalculates on every workbook change, INDEX is nonvolatile. This will significantly improve your spreadsheet's calculation speed.

Solution 2

Revert to Single-Cell References

If your workflow absolutely requires the INDIRECT function, you must abandon the spilled array notation (#) and use traditional dragged formulas.

Free Microsoft Office alternative

Switch to WPS Office for Seamless Spreadsheet Management

Avoid complex troubleshooting and expensive subscription fees. WPS Office provides a lightweight, highly compatible alternative to Microsoft Excel, offering robust support for advanced formulas, dynamic arrays, and everyday data analysis.

1. Download WPS Office: Visit the official WPS website and download the free installer for your operating system.
2. Install the application: Run the installer and follow the on-screen prompts to set up WPS Office on your device.
3. Open your Excel files: Launch WPS Spreadsheet and directly open your existing .xlsx files to continue working seamlessly.

Highly compatible with Microsoft Excel file formats (.xlsx) and formula structuresFree to use with a familiar, easy-to-navigate user interfaceLightweight installation ensures fast processing of complex calculationsBuilt-in support for dynamic array functions and modern spreadsheet features

microsoft office alternative - wps office

Frequently Asked Questions

Why does INDIRECT return a #VALUE! error with spilled arrays?

The INDIRECT function is designed to evaluate a text string as a single reference. It does not possess the logic to parse dynamic spilled array operators (like the # symbol) into a multi-cell range configuration, causing it to return a #VALUE! error.

What does the # symbol mean in a spreadsheet formula?

The # symbol is the spilled range operator. It indicates that a formula is referencing the entire dynamic array of values that "spilled" from a single source cell containing functions like FILTER, UNIQUE, or SEQUENCE.

Are INDEX and SEQUENCE better than INDIRECT?

Yes. INDIRECT is a volatile function, meaning it forces the spreadsheet to recalculate every time any change is made, which can slow down performance. INDEX and SEQUENCE are nonvolatile and integrate perfectly with modern dynamic arrays without causing unnecessary recalculations.

Excel & Data Problems