To my understanding, you mean there are many date ranges in different rows of Sheet2.
Sheet2 should look something like this.
| A | B | C
--+------------+--------------+------
1 | 1/1/2010 | 30/6/2010 | 15
2 | 1/7/2010 | 31/12/2010 | 25
3 | 1/1/2011 | 30/6/2011 | 35
4 | 1/7/2011 | 31/12/2011 | 45
As you mentioned, the date ranges are of 6-month interval, you can do this in Sheet1.
| A | B
--+-------------+-------------------------------
1 | 15/7/2010 | =VLOOKUP(A1,Sheet2!A1:C4,3)
In this solution, Sheet2!B:B
is NOT referenced. Sheet1!B1
is actually looking up in Sheet2!A:A
for the value just smaller than Sheet1!A1
(15/7/2010). As 1/1/2010 is just smaller than 15/7/2010, the result is 25.
Note: Sheet2 must be sorted for the VLOOKUP
function to work properly.
Reference: http://www.techonthenet.com/excel/formulas/vlookup.php
1Which spreadsheet program, excel? – Lance Roberts – 2010-09-08T00:09:36.917
OK, I just assumed Excel. – Lance Roberts – 2010-09-08T00:18:47.063