Using a lookup in a matrix?? 1

[Kirkebbs]

The top row represents successive dates. The first column represents the products. The table is populated with qties of the product over dates in the top row. I would like to get these values by looking them up from another table (similar in structure), however the table being populated has a finite number of columns (in otherwords it never shows more than 6 columns (ie a rolling 6 months). Whereas, the source data table has 3 years worth of months in the columns and continues to grow each month. Currently, I do use a vlookup, but each month I have to change the offset number in each column. It would be nice if the offset could simply reference the top (perhaps, another lookup) row then lookup the respective product in the first column and return the value. Is this possible? Thanks Kirk

 

[Zelandakh]

first one is successive and each column is one month later than the next?

column B is the first date and is like Oct 99 <start date>

In your 6 column summary, insert a new row. In that row you need to do [date at top of column]-[Oct 99]/30 to get number of months and that is <offset>. Adjust this to get the column number you need for the output report. Then under that you can use vlookup(a2,$source,$<offset>). Copy that through...