Formula Errors

Fix #VALUE! Error with UNIQUE, FILTER, and INDIRECT in Excel

Khadija Khan Oct 14, 2026 869 views

Question details

The user is attempting to filter a dynamic range using INDIRECT, UNIQUE, and FILTER with multiple criteria, but is encountering a #VALUE! error because the indirect reference is missing in subsequent conditions.

Fix the #VALUE! Error When Using UNIQUE, FILTER, and INDIRECT in Excel

Product: Excel
Device & OS: not provided
Scenario: Filtering out multiple specific string values from a dynamically referenced data range using an array formula.
Observed behavior: The formula evaluates to a #VALUE! error because the INDIRECT function was only declared for the initial array reference and not repeated for the subsequent criteria arrays.

Before you start

Ensure that your INDIRECT text string evaluates to a valid, existing cell or range reference, and that you are using an Excel version that supports dynamic array functions like UNIQUE and FILTER.

Solution 1Recommended

Use the LET Function to Define and Reuse the Dynamic Range

Assigning the INDIRECT result to a variable using the LET function is the cleanest way to resolve the error and prevent repetitive calculations across multiple FILTER conditions.

When filtering multiple conditions, the source array and the criteria arrays must exactly match in dimensions. If you use an INDIRECT formula for the source range but do not repeat it for the criteria logic, Excel throws a #VALUE! error.

The LET function allows you to declare a variable (e.g., 'MyRange') to store the INDIRECT calculation once. You can then reference 'MyRange' repeatedly inside your UNIQUE and FILTER functions without causing syntax errors.

Declare the LET variable

Click the cell where you want the final array to appear. Start typing '=LET(MyRange, INDIRECT("'["&B59&"]"&C59&"'!"&F59),' to assign your dynamic reference to the 'MyRange' name.

Set up the UNIQUE and FILTER wrapper

Immediately after the comma, type 'UNIQUE(FILTER(MyRange, ' to begin evaluating your newly defined range.

Apply multiple filter criteria

Multiply your conditions together within parentheses to act as 'AND' logic. For example: '(MyRange<>"") * (MyRange<>"Footer text_01") * (MyRange<>"Footer text_02")'.

Close all parentheses

Add the necessary closing parentheses for the FILTER, UNIQUE, and LET functions, then press Enter to execute the array formula.

Use the LET Function to Define and Reuse the Dynamic Range

Formula Simplified: Using the LET function not only prevents the #VALUE! error but also speeds up recalculation time since the volatile INDIRECT function is only evaluated once instead of multiple times.

Solution 2

Repeat the INDIRECT Function for Every Criteria

If you are working in an environment where the LET function cannot be used, you must manually repeat the entire INDIRECT string for each filter condition.

Advanced Formula Processing

Easily Handle Complex Dynamic Arrays with WPS Spreadsheet

WPS Office provides comprehensive built-in support for modern array functions including LET, UNIQUE, FILTER, and INDIRECT. You can smoothly manage and optimize complex dynamic data filtering without compatibility concerns.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your complex data arrays.
2. Select the target cell: Click on the specific cell where you want the filtered, unique data array to spill.
3. Enter the optimized formula: Type your optimized LET formula incorporating the INDIRECT, UNIQUE, and FILTER parameters.
4. Execute the calculation: Press Enter, and WPS Spreadsheet will instantly evaluate the dynamic array and populate the adjacent cells.

Seamless compatibility with complex Microsoft Excel dynamic array formulas.Full support for modern functions like LET, UNIQUE, and FILTER to keep spreadsheets clean.Lightweight, lightning-fast performance even when calculating volatile functions.Completely free to download and use for your everyday data processing needs.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the FILTER function return a #VALUE! error?

A #VALUE! error in the FILTER function commonly occurs when the array sizes in your include argument do not match the source array size. When using a dynamic source like INDIRECT, failing to use that identical dynamic range in the criteria causes this mismatch.

What does the LET function do in Excel?

The LET function allows you to assign a name to a calculation result or value and then use that name inside your formula. It improves performance and readability by ensuring a specific calculation is only executed once.

Can I use multiple criteria inside a FILTER function?

Yes, you can apply multiple criteria in the FILTER function using boolean logic. You multiply criteria arrays together using an asterisk (*) to represent an 'AND' condition, or add them using a plus sign (+) to represent an 'OR' condition.

Is the UNIQUE function available in all versions of Excel?

No. The UNIQUE, FILTER, and LET functions are part of dynamic arrays, which were introduced in Microsoft 365 and Excel 2021. Older versions like Excel 2016 and 2019 do not support them natively, though modern alternatives like WPS Office fully support these functions.

Excel & Data Problems