Search code examples
excelexcel-formulaexcel-2007

Is there any way to specify an offset for a cell in a named range within a formula?

Suppose I've created these named ranges: Apples = "A1:A3", Pears = "B1:B3"

Then putting the formula "=Apples + Pears" in cell C3 is the same as "=A3 + B3"

Is there some way to specify an offset in the "=Apples + Pears" formula so that cell C3 refers to "=A3 + B2" instead?

In other words, how would an offset for "Pears" be specified so that it refers to B2 instead of B3?

Thanks in advance for any help!

Edit: I would like the formula to be relative, so that if I copied it to cell C2 it would refer to "=A3 + B1"

Solution

  • You can use the ROW() function of the current cell to get an index that is automatically updated when you copy the formula. This formula goes into C3, then when you drag it up, it automatically adjusts the index for Pears.

    =INDEX(Apples,3,1)+INDEX(Pears,ROW(C3)-1,1)

    Note that if your real data doesn't start in row 2, you'll need to subtract the additional amount from the row function to allow for the additional rows.