Formula Errors

Fix Excel INDIRECT and INDEX FILTER Formula #VALUE Errors

Guest Writer Oct 9, 2026 869 views

Question details

The user needs to fix a #VALUE! error that occurs when nesting dynamic array functions like INDEX and FILTER inside an Excel INDIRECT function.

How to Fix #VALUE! Error with INDIRECT and INDEX FILTER Formulas in Excel

Product: Excel
Device & OS: not provided
Scenario: Attempting to create dynamic cell references using the INDIRECT function by passing a row number calculated via the INDEX and FILTER functions.
Observed behavior: The nested INDEX and FILTER functions calculate correctly in a helper cell, but when placed inside the INDIRECT function, they return a #VALUE! error because the formula evaluates to a one-value array instead of a scalar text reference.

Before you start

Ensure that your version of Excel supports dynamic array functions like FILTER, and verify that the inner FILTER formula correctly returns exactly one valid numeric row number before nesting it.

Solution 1Recommended

Convert the Array Result to a Text Value

Use the TEXT function to force the single-value array returned by INDEX/FILTER into a scalar text string that INDIRECT can properly process.

The INDIRECT function requires a standard text string to build a cell reference. When dynamic arrays like FILTER are nested, they can pass a 1-item array instead of a text string, which triggers a #VALUE! error.

Select the error cell

Click on the cell in your spreadsheet that currently displays the #VALUE! error to make it the active cell.

Edit the formula in the formula bar

Click into the formula bar at the top of the screen and wrap your INDEX function with the TEXT function. Modify the formula to look like this: =INDIRECT("B"&TEXT(INDEX(FILTER(tbOdometers[Row Nbr],tbOdometers[Odometer]="",MAX(tbOdometers[Row Nbr])+1),1),"0")).

Calculate the updated formula

Press the Enter key on your keyboard to apply the changes. The formula will now convert the array to text and retrieve the correct reference.

Convert the Array Result to a Text Value

Formula Validated: Forcing the array output into a "0" format text string ensures strict compatibility with the INDIRECT function parameters.

Solution 2

Use TEXTJOIN to Combine References

Use the TEXTJOIN function instead of the standard ampersand (&) operator to concatenate the column letter and the dynamic row number.

Solution 3

Calculate the Row Number in a Helper Cell

Separate the complex dynamic row calculation from the INDIRECT function to bypass nested array evaluation issues.

Advanced Formula Support

Easily Handle Dynamic Arrays and Complex Formulas in WPS Office

WPS Spreadsheets provides comprehensive support for modern dynamic array functions, including FILTER, INDEX, and INDIRECT. It efficiently processes nested formulas and offers an intuitive formula auditing tool to help you troubleshoot #VALUE! errors effortlessly.

1. Open your workbook: Launch WPS Spreadsheets and open the Excel file containing your complex formulas.
2. Select the problematic cell: Click on the cell returning the #VALUE! error to view the expression in the formula bar.
3. Apply the TEXT function fix: Wrap your INDEX calculation in the TEXT function as shown in the solutions to convert the array into a valid text string.
4. Evaluate the formula: Press Enter. WPS Spreadsheets will immediately process the calculation and return the correct dynamic reference.

Seamless format compatibility with Microsoft Excel files (.xlsx) and formulasBuilt-in error checking and formula evaluation tools for easier debuggingFull support for advanced dynamic array functions and nested calculationsLightweight, fast-loading, and completely free to use

microsoft office alternative - wps office

Frequently Asked Questions

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

The INDIRECT function strictly requires a scalar text string to form a valid cell reference. Dynamic array functions like FILTER, even when outputting a single item, may pass the result as a one-element array. INDIRECT cannot natively convert this array into a reference, resulting in a #VALUE! error.

Can I use the INDIRECT function to reference closed workbooks?

No, the INDIRECT function requires the target workbook to remain open in the background. If you attempt to reference a closed external workbook using INDIRECT, the formula will return a #REF! error.

Are there better alternatives to the INDIRECT function in Excel?

Yes. Because INDIRECT is a volatile function (meaning it recalculates every time any change is made to the workbook, potentially slowing down performance), it is often better to use the INDEX or OFFSET functions to create dynamic ranges and lookups when possible.

Excel & Data Problems