vlookup for exchange rate

[Guest]

hi,

i have a simple table: in the first column are the dates, in the second are
the exchange rates.
i would like to find the exchange rates of the last day of the months.
the problem is that rates are published only on business days, so for
example in april there is nothing for 30th of april. in this case i should
use the last business day's rate before 30th (28th of April).
could anybody some idea how could i express this in vlookup form? i tried to
use "if" formula, but i couldn't refer to result "#N/A"

thx in advance

 

[MDubbelboer]

alright, i had to use an add-in because I used the formula EOMONTH
(http://office.microsoft.com/en-us/assistance/HP052090761033.aspx)

it's in the analysis toolpack

I'll just lay out what I did
In column A I had an incrementing date range. I put from today's date
till the 28th and pretended that was the last business day of this
month
In column B I had an exchange rate
in cell C1 I put in a date. I just put it as today so 7/21/2006
in cell d1 I put in the vlookup
=VLOOKUP(EOMONTH(C1,0),A1:B23,2,TRUE)
This will return the exchange rate beside 7/28/2006.

So in column C you could put a value for each month you're interested
in and change the formatting to custom MMM-YYYY or something like
that.

Hope that helps,
Mark

 

[Fred Smith]

All you need to do is use True as the last parameter of Vlookup. When it doesn't
find an entry for the end of the month, it will use the last one available.