I'm getting an error when trying to open this, can you try uploading again please ?
1 of 1 people found this helpful
Row 4 (lookup index) ... shouldn't this display the same as Row 2 ?
No it shouldn't. If you want it to show the same as Row 2, then the formula is:
Using index() makes this lookup values farther and farther ahead on its right side:
March 2010 index() = 3 shows for example value of 3rd cell (June 2010) after current cell.
Please let me know what I'm doing wrong.
You made a wrong assumption thinking that offset is referring to an absolute index() address.
The offset parameter in Lookup is pointing to a relative position from itself.
0 is the same position. -2 is two cells before, and 1 returns the next cell's value.
The Table Calculation Functions Addressing Matrix might give you a helpful overview.
Yes, that did help, but the real problem I'm trying to solve is when I filter the table to 2012 onwards, how do I get proper values from Last Year in row 6 ? ... I'm getting the proper values there now, but when they are filtered I'm still missing the first 12 time periods.
If you want a value from a cell 12 months earlier, you should remove index():
If you want data in the first 12 months, you need to include one extra year which you hide with a window filter.