Function Problems

How to Fix Excel SCAN Function #VALUE or #NAME Error

Elise Williams Oct 9, 2026 869 views

Question details

The user encounters a #VALUE! or #NAME? error when attempting to use the SCAN function in Excel.

How to Fix Excel SCAN Function Returns #VALUE or #NAME Error

Product: Microsoft Excel
Device & OS: not provided
Scenario: Using the SCAN function with LAMBDA to calculate running totals or iterate over arrays.
Observed behavior: The formula fails and returns a #VALUE! or #NAME? error instead of the expected dynamic array or calculated result.

Before you start

Ensure your Microsoft 365 subscription is active and your software is fully updated, as the SCAN function is a dynamic array function that is not available in older Excel standalone versions like 2019 or 2016.

Solution 1Recommended

Correct the SCAN and LAMBDA Formula Syntax

The #VALUE! error typically occurs when the formula contains invalid arguments, mismatched data types, or incorrect LAMBDA syntax.

The SCAN function requires three arguments: an initial value, an array to iterate over, and a LAMBDA function to apply the logic. If any of these are missing or reference invalid data types (like text instead of numbers for addition), Excel will return a #VALUE! error.

Check the initial value

Ensure the first argument in your SCAN formula is a valid starting point for your calculation, such as '0' for a running total.

Verify the referenced array

Confirm that the second argument points to a valid range or spilled array (e.g., B1#). Ensure this range contains numbers and no error values or text strings.

Write the correct LAMBDA structure

Format the third argument properly: LAMBDA(a, b, a+b). For a complete running total formula based on a spilled range, type: =SCAN(0, B1#, LAMBDA(a, b, a+b)) and press Enter.

Correct the SCAN and LAMBDA Formula Syntax

Spilled Range Syntax: Using the # symbol (e.g., B1#) ensures the formula dynamically references the entire array spilled from cell B1, which is best practice for dynamic array functions.

Solution 2

Verify Excel Version Compatibility

The #NAME? error usually indicates that Excel does not recognize the SCAN function because your installed version does not support it.

Free Microsoft Office alternative

Try WPS Office for a Seamless Spreadsheet Experience

If your current Office version lacks support for modern functions and you are looking for a powerful, cost-effective solution, consider switching to WPS Office. It provides a lightweight, highly compatible spreadsheet environment for free.

1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite on your device.
2. Open Your Workbook: Launch WPS Spreadsheets and open your existing .xlsx files directly without any conversion.
3. Edit and Calculate: Continue editing your formulas and analyzing your data with excellent format compatibility and performance.

Free to use with a familiar, easy-to-navigate tabbed interface.Highly compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Supports a vast library of standard and advanced formulas for data analysis.Lightweight design ensures fast performance even with large datasets.

microsoft office alternative - wps office

Frequently Asked Questions

What does the SCAN function do in Excel?

The SCAN function applies a LAMBDA function to each value in a specified array and returns an array that contains each intermediate value. It is most commonly used for calculating running totals.

Why does Excel show a #NAME? error instead of my formula result?

The #NAME? error typically appears when you misspell a function name or when you are using an older version of Excel (like Excel 2016 or 2019) that does not recognize newly introduced dynamic array functions like SCAN or LAMBDA.

How do I fix the #VALUE! error in my LAMBDA function?

Check that the arguments passed to your LAMBDA function match the expected data types. For instance, if you are performing addition (a+b), ensure the referenced array contains actual numbers, not formatted text strings or blank spaces.

Can I use the SCAN function on non-contiguous ranges?

No, dynamic array functions like SCAN require a single contiguous array or range of data to iterate over properly. Attempting to use non-contiguous ranges will result in formula errors.

Excel & Data Problems