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:1048576 (in recent versions of Excel) or 1:65536 (in Excel 2003). As these references are Relative References, Excel 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:1048576 is copied down, the row references automatically increase to 2:1048577, 3:1048578, ...

Similarly for Excel 2003, 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, ...

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

Solution:

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

  • Add the dollar symbols to change the references to absolute references (ie. change the reference to $1:$1048576 in recent versions of Excel or $1:$65536 in Excel 2003).
    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.
   or
  • Change your reference to use only the columns you need (eg. A:D)
   or
  • 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.