In my workbook, columns 2-12 are the months of sales, column 13 is the lead time (the time it will take me to receive the goods once ordered).

I want to write a formula that will calculate the number of months of lead time, then to count the appropriate number of columns of months


For instance, if it's May 28, the lead time is 10 weeks, then I would like to see 12-(10/4) = 10 months ago. So go look at Last July to see what the sales were in that month to project historical needs.

The issue is that since each Item has a specific lead time (10 in this instance) which is a column, then I need to figure out how to use column 4 as my sales projection month.

When an Item has a 16 week lead time, I'll need to use column 6...and so on.

I'd like the formula to take the # of months ago and then COUNT THAT number of columns to the left.

Is there a way to do that?