Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default vlookup for exchange rate

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


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default vlookup for exchange rate


alright, i had to use an add-in because I used the formula EOMONTH
(http://office.microsoft.com/en-us/as...090761033.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


--
MDubbelboer
------------------------------------------------------------------------
MDubbelboer's Profile: http://www.excelforum.com/member.php...o&userid=36330
View this thread: http://www.excelforum.com/showthread...hreadid=563743

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 623
Default vlookup for exchange rate

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.

--
Regards,
Fred


"Gábor" wrote in message
...
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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
VLookup a Vlookup adamb2000 Excel Worksheet Functions 4 June 28th 06 10:54 PM
VLOOKUP Problem Ian Excel Discussion (Misc queries) 3 April 6th 06 06:47 PM
XNPV vs. NPV(quarterly) different results magis Excel Worksheet Functions 3 January 6th 06 06:21 PM
VLOOKUP using a cell calculated with NOW returns Error Chris Berding Excel Worksheet Functions 2 August 21st 05 03:22 AM
vlookup data hidden within worksheet Excel Worksheet Functions 0 January 26th 05 01:09 PM


All times are GMT +1. The time now is 11:08 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"