Dynamic Range Lookup

[Chris86t]

I have a tab with multiple weeks of data. I have created a vlookup to lookup the previous weeks data and bring back a certain field.

Currently I'm having to manually change the range each week which is a pain and prone to error. Is there a way that I can make the range more dynamic.

In column A of my data I have the week the data is relating to so is there a way to use that in order to automate the lookup range. I thought of creating a range called previous_week and using that however i'm unsure how to make the range dynamic

=IFERROR(IF($F76250="F",$X76250,VLOOKUP($D76250,$D$71407:$X$73828,21,0)),$X76250)

Any help would be greatly appreciated

 

[CA_Punit]

How does the range change???? Is there addition in range or deletion in ranges.
Post a sample of ranges

Also is there any specific criteria for selection of ranges.

 

[Chris86t]

The range will likely change either increasing or decreasing in size week on week.

the criteria is based on a weekly date in column A. Every Monday I paste new data into column B and add the week commencing date.

thanks