Vlookup when a new data column is added each month

Hi All,

My issue is that I am trying to do a vlookup against a dataset which is a rolling 24 months. So currently columns H to AE contain product sales data for July 2012(H) to June 2014(AE). I could easily do a vlookup if this data was stand alone but every month the 24 months changes and next month the dataset will be August 2012(H) to July 2014(AE).

I am trying to do a vlookup to return all the months values in the current year, so this will currently be six values (Jan to Jun 2014).

Anyone any ideas of how I will do this? I'm guessing I will need some sort of dynamic vlookup but I can't see now how I will work this out.

Any help is much appreciated, thanks all.

current year by month.xlsxcurrent year by month.xlsxtry this

This is really good and is very close to what I am looking for, I have two issues with the formula provided though:

Something I failed to mention

1. The sum product should take into account if the product appears more than one in the dataset.

2. The sum formula looks at TODAY, one thing I failed to mention is that I receive my data one months in arrears. So in Feb I receive Jan data, and in Mar I receive Feb data.

The issue is that when I will receive Dec 2014 data in Jan 2015 and the formula looks at TODAY it will find 2015 and will not capture the December 2014 data."

Thanks.

current year by month II.xlsx

Originally Posted by bkeat

This is really good and is very close to what I am looking for, I have two issues with the formula provided though:

Something I failed to mention

1. The sum product should take into account if the product appears more than one in the dataset.

2. The sum formula looks at TODAY, one thing I failed to mention is that I receive my data one months in arrears. So in Feb I receive Jan data, and in Mar I receive Feb data.

The issue is that when I will receive Dec 2014 data in Jan 2015 and the formula looks at TODAY it will find 2015 and will not capture the December 2014 data."

Thanks.

1. added criteria for product
2. added simple if statement to resolve the current year issue
3. tab one uses the sumproduct function, tab 2 uses "sumifs".
If the data base is large, sumifs will be quicker.

Thanks for your help this has worked.