Formula Errors

Fix #VALUE! Error When FILTER Uses an Array in Excel

John Wilson Oct 16, 2026 872 views

Question details

The user encounters a #VALUE! error when attempting to select specific columns from a FILTER function output using an array.

How to Fix #VALUE! Error When FILTER Uses an Array in Excel

Product: Excel
Device & OS: not provided
Scenario: Filtering a dataset to return specific columns dynamically, but the array orientation or syntax triggers a calculation error.
Observed behavior: Excel returns a #VALUE! error because the array used to select columns either has the wrong orientation (vertical instead of horizontal) or contains incorrect regional list separators.

Before you start

Verify your computer's regional settings to check if your default formula separator is a comma or a semicolon, as this determines the correct syntax for your array constants.

Solution 1Recommended

Use the CHOOSECOLS Function

If your version of Excel supports modern array functions, CHOOSECOLS is the cleanest and most reliable way to select specific columns from a filtered dataset.

The CHOOSECOLS function allows you to specify exactly which columns to return from an array, avoiding the complexities and potential syntax errors of nested boolean arrays.

Identify your base FILTER formula

Start with your working FILTER formula that pulls the initial data, for example: =FILTER(B:F, (B:B>1)*(D:D>1))

Wrap with CHOOSECOLS

Insert the CHOOSECOLS function around your FILTER formula and specify the column index numbers you want to keep. Example: =CHOOSECOLS(FILTER(B:F, (B:B>1)*(D:D>1)), 1, 3, 5)

Apply the formula

Press Enter. The formula will spill dynamically to return only columns 1, 3, and 5 from your filtered dataset without returning a #VALUE! error.

Version Compatibility: CHOOSECOLS is available in Microsoft 365 and modern versions of Excel and WPS Spreadsheet. If you are using an older version, use the nested FILTER method instead.

Solution 2

Use a Nested FILTER with a Horizontal Array

Use this method to extract specific columns by nesting your primary FILTER function inside a secondary one that uses a horizontal array of 1s and 0s.

Solution 3

Adjust Separators Based on Locale Settings

Resolve #VALUE! and syntax errors caused by regional settings that use semicolons instead of commas as list and array separators.

Seamless Data Analysis

Easily Filter and Manipulate Arrays with WPS Spreadsheet

WPS Office fully supports dynamic arrays and advanced filtering functions like FILTER and CHOOSECOLS, allowing you to manipulate complex data sets efficiently without syntax headaches.

1. Open your dataset: Launch WPS Spreadsheet and open the file containing the dataset you need to filter.
2. Select the target cell: Click on an empty cell where you want the top-left corner of your filtered results to appear.
3. Enter the modern array formula: Type the formula =CHOOSECOLS(FILTER(DataRange, Condition), 1, 3) substituting your actual ranges.
4. View your spilled results: Press Enter. WPS Spreadsheet will automatically spill the filtered and selected columns directly into the worksheet.

100% compatible with Microsoft Excel formulas and array functionsBuilt-in support for dynamic array spilling like CHOOSECOLS and FILTERLightweight, fast, and completely free to use for everyday data analysis

microsoft office alternative - wps office

Frequently Asked Questions

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

A #VALUE! error in the FILTER function typically means that the filter criteria array does not match the dimensions (rows or columns) of the source array, or an array constant used for selection has an incorrect orientation (e.g., vertical instead of horizontal).

What is the difference between a vertical and horizontal array in Excel?

In Excel array constants, separators determine orientation. In standard US locales, commas separate items horizontally across columns (e.g., {1,2,3}), while semicolons separate items vertically down rows (e.g., {1;2;3}). Using a vertical array to filter horizontal columns results in a #VALUE! error.

How do I extract non-adjacent columns with the FILTER function?

The easiest way is to wrap your FILTER formula inside a CHOOSECOLS function, specifying the exact column numbers you want. For example, =CHOOSECOLS(FILTER(A1:E100, A1:A100="Yes"), 1, 4) will extract only the 1st and 4th columns of the filtered result.

Why is my array constant syntax invalid in different locales?

Excel relies on your Windows regional settings for list separators. In regions where a comma is used for decimals (like many European countries), Excel formulas must use a semicolon to separate function arguments, and typically use a backslash (\) or period (.) to separate columns in array constants.

Excel & Data Problems