I have this formula =IFERROR(VLOOKUP("Actual/Estimated Ship Units Forecast",'WD ALPS'!$A:$K,MATCH(Date1,'WD ALPS'!$A$651:$K$651,0),FALSE),"") Each time I run a macro to re-pull the data referenced in this formula, excel changes the range by one. (which messes up all of my calculations.) So I go from MATCH(Date1,'WD ALPS'!$A$651:$K$651,0) to MATCH(Date1,'WD ALPS'!$A$650:$K$650,0) without reason. I want it to stay constant no matter what happens to the data sheet.

CodePudding user response：

One possibility is to use OFFSET. This is a volatile function, which may result in response time issues, but at a small scale, it is usually not an issue.

You would lock the OFFSET to an anchor cell that is not changed by the macro that you run. In this case, I will assume A1 is not impacted. You could change your formula to:

MATCH(Date1,OFFSET('WD ALPS'!$A$1,650,0):OFFSET('WD ALPS'!$A$1,650,10),0)

Another way that would not be volatile and would not have a dependency on an anchor would be to use INDEX in the same way:

MATCH(Date1,INDEX('WD ALPS'!$1:$1048576,651,1):INDEX('WD ALPS'!$1:$1048576,651,11),0)

This selects the whole sheet and then cuts out the piece that you want with absolute, fixed indices. The down side is that it evaluates the whole sheet - I am not sure which would run faster.