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
Possible Reason :
#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
(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
Similarly, when a cell containing the reference
1:1048576 is copied down, the row references
automatically increase to
As these increased row numbers (
65538 in Excel 2003 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: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.
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.