'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Fix the Excel #NAME? Error in the SCAN Function
Question details
The user needs to resolve a #NAME? error that occurs when utilizing the SCAN function in Excel.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Applying the SCAN function to process array calculations.
- Observed behavior
- The SCAN formula fails and returns a #NAME? error, typically due to an invalid LAMBDA argument or because the function is unavailable in the current Excel version.
Verify that you are using an active Microsoft 365 subscription, as the SCAN function is a dynamic array feature not supported in older standalone versions like Excel 2016 or 2019.
Format the SCAN Function with a Valid LAMBDA Argument
Use this solution to ensure your formula structure is correct, as the SCAN function strictly requires a LAMBDA function for its accumulator argument.
The SCAN function iterates over an array and applies a LAMBDA function to each value, returning an array that contains each intermediate value. If the third argument is not formatted as a valid LAMBDA function, Excel cannot parse the formula and throws a #NAME? error.
Click on the cell where you want the SCAN function results to appear.
Type your SCAN function and ensure the third argument is a LAMBDA. For example, use: =SCAN(H3,H4:H8,LAMBDA(a,b,SUM(a,b)))
Press Enter to calculate the formula. The #NAME? error should now be resolved, and the intermediate accumulated values will spill into the adjacent cells.
Update Microsoft 365 to the Latest Version
If your formula is perfectly formatted but still returns an error, your Excel client may be missing recent feature updates.
Try WPS Office for Seamless Spreadsheet Management
If your current version of Microsoft Office lacks the latest array functions or you are looking for a more cost-effective solution, consider switching to WPS Office. It provides powerful data analysis tools in a highly compatible, fast, and familiar environment.
- 1. Download the software: Visit the official WPS Office website to download the free, lightweight installation package.
- 2. Open your files: Launch WPS Spreadsheet and directly open your existing Excel documents without needing to convert file formats.
- 3. Analyze your data: Continue using standard formulas and analyzing your data with ease in a familiar layout.
Frequently Asked Questions
Why does Excel show a #NAME? error?
The #NAME? error occurs when Excel cannot recognize text inside a formula. This usually happens if a function name is misspelled, a defined name is missing, or you are trying to use a newer function (like SCAN or LAMBDA) that your current version of Excel does not support.
Is the SCAN function available in Excel 2019 or 2021?
No. The SCAN function is part of the newer dynamic array functions and is exclusive to Microsoft 365 subscriptions and Excel for the Web. Standalone versions like Excel 2016, 2019, and 2021 do not support it.
What is the role of LAMBDA in the SCAN function?
The SCAN function relies on a LAMBDA function as its third argument to act as the accumulator. The LAMBDA defines exactly how the intermediate values should be calculated (e.g., adding or multiplying them) as the SCAN function iterates through the provided array.
