Lookup Function Won't Copy Down to Other Rows

Have you ever encountered this Excel problem ...

... You use a function in one cell and it works perfectly. However, when you attempt to copy the function down to other rows, you get the #REF error.

Possible Reason :

The #REF! error arises when Excel is attempting to reference an invalid cell.

If you are copying a formula that works, down to other rows, the #REF! error suggests that you are accessing cells that don't exist.

This will occur if you have referenced an entire worksheet by clicking on the grey square at the top left of the worksheet. In this case, the reference to the worksheet is 1:65536 (in Excel 2003) or 1:1048576 (in Excel 2007 or Excel 2010). As these references are Relative References, Excel will automatically increases the row references when this cell is copied down to other rows in the spreadsheet.

Therefore, when a cell containing the reference 1:65536 is copied down to other rows, the row references are automatically increased to 2:65537, 3:65538, ...

Similarly, when a cell containing the reference 1:1048576 is copied down, the row references automatically increase to 2:1048577, 3:1048578, ...

As these increased row numbers (65537 & 65538 in Excel 2003 or 1048577 or 1048578 in Excel 2007 or Excel 2010) don't exist, this causes the #REF! error to be generated.


Check the formula in the first cell, that works. If you are referencing the cell range 1:65536 (or 1:1048576 in Excel 2007), then either :

  • Add the dollar symbols to change the references to absolute references (ie. change the reference to $1:$65536 in Excel 2003, or to $1:$1048576 in Excel 2007).
    Note: the $ signs prevent Excel from increasing the row reference as the formula is copied down to other cells - read more about this on the Excel Cell References page.
  • Change your reference to use only the columns you need (eg. A:D)
  • Change your reference to use only the cells you need (eg. A$1:D$100) - but ensure you use the $ signs before the row numbers in this reference.
    This solution is the most efficient, as when Excel is searching through the range of cells, it has fewer cells to search through.
Return to the Excel Errors Page

Return to the ExcelFunctions.net Home Page