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