Formula problem

[Andrew Mackenzie]

OK Folks, Monday morning and my brain is not functioning properly so I am
turning to you guys for help.

I have a table of exchange rates that looks something like this

EUR USD CHF
30/11/2008 1.2108 1.5396 1.8709
31/12/2008


The whole table has been named dtiExchangeRates.

Elsewhere I have date that looks like this

Joe Blogs EUR 1000 20/11/2008
Eric Sykes CHF 3000 25/11/2008

I want to put a formula in the fifth column which will calculate the
sterling equivalent of the amount in the third column by reference to the
appropriate currency. Thus the result in the fifth column would be 825.90
for Joe Blogs and 1,603.51 for Eric Sykes.

I assume that some kind of Index/Match combination would do it but my head
is hurting trying to figure it out. Any help would be much appreciated.

Many thanks in advance,

Andrew

 

[Jarek Kujawa]

yr assumption is correct

but in my opinion to achieve that you would also need a GBP exchange
rate which was not provided

presume it is in in the 5th column of 1st table

then try (yr clients' data in cols A through D, exchange rates cols H
and farther):

=C1*OFFSET($H$1,MATCH(D1,H2:H10,),MATCH("GBP",I1:L1,))

should work

HIH

 

[Andrew Mackenzie]

Thanks Jarek, worked perfectly.

 

[Roger Govier]

Hi Andrew

I created the table of currencies on Sheet2 A13

I placed the other values on Sheet1 starting at A1, and in E1 used the
following
=C1/INDEX(Sheet2!$A$1:$D$3,MATCH($D1,Sheet2!$A$2:$A$3,-1),
MATCH($B1,Sheet2!$A$1:$D$1,0))
and copied down

I think it makes it easier to follow if you create some named ranges
Insert>Name>Define
Name Exchange
Refers to =Sheet2!$A$1:INDEX(Sheet2!$D:$D,COUNTA(Sheet2!$A:$A)+1)
Name Dates
Refers to =Sheet2!$A$2:INDEX(Sheet2!$A:$A,COUNTA(Sheet2!$A:$A)+1)
Name Currency
Refers to =Sheet2!$A$1:INDEX(Sheet2!$1:$1,COUNTA(Sheet2!$1:$1)+1)

These are dynamic ranges so they will grow as you add more values for other
dates, or if you add other columns for more currencies. (They all assume
that A1 in the Exchange table will be blank - hence the +1's)

The formula then becomes much easier to read and manage
=C1/INDEX(Exchange,MATCH(D1,Dates,-1),MATCH(B1,Currency,0))