if logical test true, then hlookup, if false then difference betwe
 

MSExcel2002 SP3
This is a confusing scenario, I'll do my best to explain.

A B C D
4 May-05 Jun-05 Jul-05

6 1-month 2.913% 3.298% false

B6 =IF(B4<'Qtr Lookup Box'!$A:$A,HLOOKUP(B4,'bloomberg swap
curve'!$H$4:$AA$16,4),"false")
C6 =IF(C4<'Qtr Lookup Box'!$A:$A,HLOOKUP(C4,'bloomberg swap
curve'!$H$4:$AA$16,4),"false")
D6 =IF(D4'Qtr Lookup Box'!$A:$A,HLOOKUP(D4,'bloomberg swap
curve'!$H$4:$AA$16,4),"false")
Where I have < I would need to use a "Like" or "=" formula. Because it is
not a number I am referring to but a date, then I don't know if it is
intelligent enough to treat it as a pure match "=" formula. I am trying to
test if C4 is within a lookup table, if the value is there then it would
return the appropriate value of the Hlookup formula, if false then for now I
have it written to return "false". I haven't been able to return a good
"true" answer within the if formula so I wasn't wasting time right now to
create the "false" response.

The external table (Qtr lookup Box) is just a row of dates by quarter end
dates 31-Mar-2005 / 30-Jun-2005 / 30-Sep-2005 / 31-Dec-2005 etc. for 10 years
out in row format (1 column).

The ideal goal is if the date is a quarter end date to look up the real # in
the bloomberg swap curve tab. If the date is not the quarter end date then
it would take the prior month rate +( 2 months forward less prior month )
divide by 3.
If H6 =G6+((J6-G6)/3)

If I try this formula in D6 I get a circular reference.
=IF(D4'Qtr Lookup Box'!$A:$A,HLOOKUP(D4,'bloomberg swap
curve'!$H$4:$AA$16,4),C6+((F6-C6)/3))

This is a snipit of the swap curve tab:
the first two rows of my past did not line up perfect here but you should
get the gist. I've listed the row and column associations. (there were
hidden columns)

A C H I J
05/09/05
4 Current Mar-05 Jun-05 Sep-05
5 Prime 6.00% 5.75% 6.25% 6.75%
6 Fed Funds 3.00% 2.75% 3.25% 3.75%
7 1 Month Libor 3.09% 2.91% 3.30% 3.72%
8 3 Month Libor 3.25% 3.10% 3.44% 3.78%
9
10 1 Year Libor 3.80% 3.74% 3.83% 3.99%
11 2 Year Swap 4.10% 4.14% 4.05% 4.16%
12 3 Year Swap 4.23% 4.37% 4.18% 4.26%
13 4 Year Swap 4.32% 4.51% 4.26% 4.34%
14 5 Year Swap 4.41% 4.65% 4.35% 4.42%
15 7 Year Swap 4.53% 4.79% 4.48% 4.54%
16 10 year Swap 4.69% 4.98% 4.65% 4.71%

So if the rate is not listed in this table then the formula should be a
blended average of the month past and forward.

I hope this was clear and detailed so that I can soon find a resolution.

Thank you, Victoria