Friday, November 18, 2011

yogi_Fetch A Value Based On A Date Range

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user thirdbridge said:
Fetch a value based on a date range
On Sheet1, I have a column of bagels. For each type of bagel, there is a series of date / baker's dozen price / single price groups of data - where the date in each triple column group represents the date the new price for the baker's dozen went into effect. The single column is a baker's dozen calculation simply dividing by 12 to get the single price. See below:
A (Bagel) B (Date) C (12) D (1) E (Date) F (12) G (1) H (Date) I (12) J (1)
1 Plain 6/1/11 $15 $1.25 8/19/11 $17 $1.42 11/1/11 $22 $1.83
2 Blueberry 4/15/11 $15 $1.25
3 Everything 6/1/11 $15 $1.25 10/12/11 $17 $1.42
4 Whole Grain 5/25/11 $15 $1.25 7/19/11 $19 $1.53 11/1/11 $22 $1.83
On Sheet2, I have a report with a date at the top. The date falls between the dates listed above.
How can I reference the dates on Sheet1 and return the corresponding single bagel price that fits the date listed on Sheet2?
For example: if the date on Sheet2 is 9/1/11, how do I show that the price of the Plain bagel on that day was $1.42?
Thanks!
---------------------------------

follwing is one solution to the problem ...