Excel: Problems with autofill, when reference cells increment by 3 (vlookup)

Question

I'm trying to improve my Excel skills (and impress my boss, of course) as I'm working on a pretty large data set with a few sheets of raw material and many sheets of statistics from the raw material. I've been handed an Excel sheet that doesn't make my life simpler, as someone else has made the layout.

I want to fetch data from Sheet1 and calculate them on Sheet2. The raw data is presented like this:

   A   B     C      D   E     F      G   H     I
1| Week 1           Week2            Week 3
2| ID  Name  Value  ID  Name  Value  ID  Name  Value
3| 1   A     15     2   B     4      1   A     3
4| 2   B     9      3   C     14     3   C     23
5| 4   D     1      5   E     2      4   D     8
6| 7   G     16     6   F     7
7|                  9   I     2

The data sets (weeks) has different number of rows, as the source only logs if there is data (no 0's). So to get the data in the right place, I'm using a vlookup on every ID which checks each week for ID and writes the value. I'm using this for each ID, which works (ID 1 below, with IFERROR to write 0 where there is no data):
=IFERROR(VLOOKUP(1;Sheet1!A3:C6;3;FALSE);0)
The end row (here C6) will vary, so I might make it simple and chose a higher number (although not very sophisticated).

My problem is that I want to autofill this to the next cell for Week2 and Week3 (and so on, for many weeks), which would be:
=IFERROR(VLOOKUP(1;Sheet1!D3:F6;3;FALSE);0) and
=IFERROR(VLOOKUP(1;Sheet1!G3:I6;3;FALSE);0).

But autofill insists on making the next cell =IFERROR(VLOOKUP(1;Sheet1!B3:D6;3;FALSE);0).

Is there any smart way of incrementing the autofill by 3(A:C->D:F), as is the pattern, instead of 1 (A:C->B:D)?

Answer 1

There are various methods to offset columns; I prefer the non-volatile¹ INDEX function.

=IFERROR(VLOOKUP($K5, INDEX($A:$ZZ, 0, (COLUMN(A:A)-1)*3+1):INDEX($A:$ZZ, 0, (COLUMN(A:A)-1)*3+3), 3, FALSE), 0)

---

¹ _{Volatile functions recalculate whenever anything in the entire workbook changes, not just when something that affects their outcome changes. Examples of volatile functions are INDIRECT, OFFSET, TODAY, NOW, RAND and RANDBETWEEN. Some sub-functions of the CELL and INFO worksheet functions will make them volatile as well.}