Fix Microsoft Excel 365 #SPILL! Errors Caused by the ROW Function
Question details
The user needs to prevent a legacy ROW formula from returning multiple row numbers and triggering a #SPILL! error in Microsoft Excel 365.

- Product
- Microsoft Excel 365
- Device & OS
- not provided
- Scenario
- A formula uses ROW with a multi-cell range together with MATCH to calculate the worksheet row of a matching value.
- Observed behavior
- ROW(kW!DE205:DE326) returns an array of row numbers in Excel 365, so the formula attempts to spill multiple results instead of returning one value.
Confirm that DE46 contains the value you want to find and that kW!DE205:DE326 is the intended lookup range. Use an exact-match value that appears only once if you need a single worksheet row result.
Reference the first cell in the ROW calculation
Use this method when the formula should return the row number of the item matched in kW!DE205:DE326.
In Microsoft Excel 365, ROW(kW!DE205:DE326) produces an array containing every row number in the range. Referencing the first cell gives ROW a single-cell reference, while MATCH supplies the position of the matched item.
Click the cell that currently contains the formula producing the #SPILL! error.
In the formula bar, enter =ROW(kW!DE205)+MATCH(DE46,kW!DE205:DE326,0)-1.
Press Enter. Excel returns one worksheet row number instead of spilling the row numbers for the entire DE205:DE326 range.

Use INDEX to explicitly select the first cell of the range
Use this alternative when you want the formula to show clearly that only the first cell of the lookup range is used as the starting row.
Work with Excel Formula Files in WPS Spreadsheets
If you want a free, lightweight spreadsheet alternative for opening and editing Microsoft Excel workbooks, WPS Office provides a familiar spreadsheet interface and seamless migration for common workbook tasks. Open the file in WPS Spreadsheets to review formulas and continue working with compatible Excel formats.
- 1. Install WPS Office: Download and install WPS Office, then open WPS Spreadsheets from the WPS Office home screen.
- 2. Open the Excel workbook: Select Open and choose the Microsoft Excel .xlsx or .xls file that contains the formula.
- 3. Review the formula cell: Select the affected worksheet cell and use the formula bar to inspect or update the ROW and MATCH formula.

Frequently Asked Questions
Why does ROW(kW!DE205:DE326) cause a #SPILL! error in Excel 365?
Excel 365 treats ROW applied to a multi-cell range as a dynamic-array result. ROW(kW!DE205:DE326) returns the row numbers for all cells in that range, so Excel tries to place multiple results in neighboring cells.
What formula returns the worksheet row of the value found in DE46?
Use =ROW(kW!DE205)+MATCH(DE46,kW!DE205:DE326,0)-1. ROW(kW!DE205) provides the starting row, and MATCH returns the matched value's position in the range.
Do I need to add the @ operator to fix this ROW formula?
No. Referencing the first cell directly, such as ROW(kW!DE205), returns one row number and avoids relying on the implicit intersection operator.
What happens if DE46 is not found in kW!DE205:DE326?
MATCH with 0 requires an exact match. If DE46 does not appear in kW!DE205:DE326, the formula returns a #N/A error.
Can this formula return the wrong result when the lookup value appears more than once?
Yes. MATCH(DE46,kW!DE205:DE326,0) returns the position of the first exact match only. Use a unique lookup value or a different formula if you need to identify later duplicates.




