I am trying to solve the following problem:
I have the following two tables, the first table has the data I will be using, and the second table is the table I wish to sort my data into
(Sheet 1) Table 1
<tbody>
</tbody>
(Sheet 2) Table 2 for Curve ID 14
<tbody>
</tbody>
What I am trying to do is fill table 2 with the zero rate that corresponds with the correct date and the DaystoMaturity closest to the column value. For example, in cell D6 on Sheet 2, I wish to place the zero rate 0.0402 as that was the zero rate on 3/28/2006 with 9 days to maturity, which is closest to 7 out of the possible days to maturity in table 2. Furthermore, I need to check whether or not the CurveID is 14, if it is, include the value, otherwise do not.
I need help figuring out a formula that can do this, I tried some nested vlookup stuff, but am unsure how to incorporate the fact that the days to maturity do not necessarily correspond exactly.
Thanks for any help!
I have the following two tables, the first table has the data I will be using, and the second table is the table I wish to sort my data into
(Sheet 1) Table 1
Date | CurveID | DaystoMaturity | ZeroRate |
3/24/2006 | 14 | 1 | 0.0102 |
3/24/2006 | 31 | 7 | 0.0412 |
3/27/2006 | 14 | 31 | 0.0101 |
3/27/2006 | 31 | 62 | 0.0231 |
3/28/2006 | 14 | 9 | 0.0402 |
3/29/2006 | 14 | 6 | 0.0103 |
3/29/2006 | 31 | 28 | 0.0314 |
<tbody>
</tbody>
(Sheet 2) Table 2 for Curve ID 14
1 | A | B | C | D | E |
2 | DaystoMaturity | ||||
3 | Date | 0 | 1 | 7 | 30 |
4 | 3/24/2006 | ||||
5 | 3/27/2006 | ||||
6 | 3/28/2006 | ||||
7 | 3/29/2006 |
<tbody>
</tbody>
What I am trying to do is fill table 2 with the zero rate that corresponds with the correct date and the DaystoMaturity closest to the column value. For example, in cell D6 on Sheet 2, I wish to place the zero rate 0.0402 as that was the zero rate on 3/28/2006 with 9 days to maturity, which is closest to 7 out of the possible days to maturity in table 2. Furthermore, I need to check whether or not the CurveID is 14, if it is, include the value, otherwise do not.
I need help figuring out a formula that can do this, I tried some nested vlookup stuff, but am unsure how to incorporate the fact that the days to maturity do not necessarily correspond exactly.
Thanks for any help!
Last edited: