Excel Error Codes

How to Fix the #CALC! Error in Excel VSTACK and FILTER Formulas

Partner Editor Oct 1, 2026 871 views

Question details

The user needs to fix a #CALC! error that appears when combining VSTACK and FILTER to compare two columns and extract unique values.

How to Fix the #CALC! Error in Excel VSTACK and FILTER Formulas

Product: Microsoft Excel
Device & OS: not provided
Scenario: Using a VSTACK and FILTER formula to compare two columns and return values that occur in only one of the columns.
Observed behavior: The formula produces a #CALC! error when one of the FILTER functions finds no matching results to return.

Before you start

Ensure you are using a version of Excel that supports dynamic array formulas, such as Microsoft 365 or Excel 2021 and later, before working with VSTACK and FILTER.

Solution 1Recommended

Provide an Empty-Result Value in the FILTER Function

Use the third argument of the FILTER function to specify what should be returned when no results are found, which prevents the #CALC! error.

By default, the FILTER function returns a #CALC! error if the filter criteria result in an empty array. To prevent this when stacking multiple filtered arrays using VSTACK, you must define the [if_empty] argument for each FILTER function.

Locate the formula

Select the cell containing your current VSTACK and FILTER formula that is returning the #CALC! error.

Edit the first FILTER function

Inside the formula bar, add a comma after your filter criteria, and type two double quotes ("") to return a blank cell when no matches are found.

Edit the second FILTER function

Repeat this process for the second FILTER function inside your VSTACK formula.

Apply the corrected formula

Your final formula should look similar to this: =VSTACK(FILTER(A4:A98,NOT(COUNTIF(L4:L107,A4:A98)),""), FILTER(L4:L107,NOT(COUNTIF(A4:A98,L4:L107)),"")). Press Enter to apply.

Provide an Empty-Result Value in the FILTER Function

Custom Text Alternative: Instead of empty double quotes, you can also use custom text like "No Match" in the third argument to display a specific message when a column yields no unique values.

Solution 2

Use the UNIQUE Function as an Alternative

If you are simply trying to extract values that only appear once across both lists, the UNIQUE function can serve as a cleaner alternative.

Powerful Spreadsheet Tool

Handle Dynamic Arrays Seamlessly in WPS Office

WPS Spreadsheet offers comprehensive support for modern array formulas, allowing you to confidently use dynamic functions like FILTER and UNIQUE to analyze your data without compatibility issues.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office, open your spreadsheet file, and navigate to the worksheet containing your data lists.
2. Enter the dynamic formula: Click on an empty cell where you want the results to spill, and type your formula using FILTER or UNIQUE.
3. Press Enter to spill results: Hit Enter, and WPS Spreadsheet will automatically calculate and spill the array results into the adjacent cells instantly.

Fully compatible with Microsoft Excel (.xlsx) formulas and file formatsSupports dynamic array behavior for complex data filtering and sortingLightweight, fast, and features a familiar tabbed user interfaceFree to download and use for your daily productivity needs

microsoft office alternative - wps office

Frequently Asked Questions

Why does the FILTER function return a #CALC! error in Excel?

The #CALC! error occurs because the FILTER function successfully evaluates your dataset but finds no rows that meet your specified criteria. Because it has nothing to return, it produces an error unless you provide a fallback value in its optional third argument.

What does the third argument in the FILTER function do?

The third argument, known as [if_empty], tells Excel what value to display if the filter criteria yield zero results. Providing a value here, such as "" (a blank string), prevents the formula from outputting a #CALC! error.

Does VSTACK cause the #CALC! error?

No, VSTACK simply stacks arrays vertically. In a combined VSTACK and FILTER formula, the #CALC! error originates from the FILTER function when it evaluates to an empty array. Correcting the FILTER function resolves the issue for the entire VSTACK formula.

Microsoft Office Error Codes