Hi Osiso, Can you just clarify what you are trying to do, as it sounds like we are trying to make this more difficult than it needs to be.
Presumably you have an array of exchange rates from date x (eg 199712) to date y (eg 200712) on one sheet. On another sheet you are wanting to pull back the rates for two particular year/month(s) to compare. The easiest way would be to have 2 input cell to define the year / month you wish to call back which your vlookup(s) could utilise. I think this would be beneficial as it would help as to clearly visualise the reference. The alternative would be the following: M4=if(value(right(m1,2))=1,vlookup(value((left(m1,4)-1) &12),fx,M2,false),vlookup(m1-1,fx,m2,false)) Alvin On Aug 5, 3:41 am, osiso <fahe...@gmail.com> wrote: > Problem: > I want to calculate currency difference for yrs 1997-2007. Currently > this is done via: > > Sheet9! below > a b c > 1 Date AUS USA > 2 199712 0.782 1 > 3 199801 0.761 1 > . > . > . > n 200712 > =this matrix is named 'fx' > > Sheet3! below > > M1=199801 > M2=2 (for the column to look up) > M3=vlookup(M1,fx,M2,false) > M4=vlookup(M1-1,fx,M2,false) > > M3 works fine, > M4 crashes in 199801. > > Any suggestions? I have .csv files saved YYYYMM (year month).csv so > changing the dates would be tedious. --~--~---------~--~----~------------~-------~--~----~ ---------------------------------------------------------------------------------- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 5,200 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~----------~----~----~----~------~----~------~--~---
