Different currency count on multiple sheets

[tonio11]

Hi

I have a bank transaction workbook for accounts in

sheet 1 =USD,

Info	Name	Date	Info2	Transaction	Debit	Deposit	Currency
Bank fee	Banka Koper d.d.	01.01.11.	Obresti pozitivnega stanja	1101011118236389	0.00	1.41	USD
Bank fee	Banka Koper d.d.	03.01.11.	Tromesečno izplačilo realnih obresti	1101031118790032	0.00	0.18	USD
Wages	Mladen Anzulovic	03.01.11.	Wages DPG 11-10	1101031119551607	4,060.00	0.00	USD
Bank fee	Banka Koper d.d.	03.01.11.	Ostale plačilne transakcije na drugo banko - i-Net	1101031119551607	12.83	0.00	USD

sheet 2 =EUR,

Info	Name	Date	Info2	Transaction	Debit	Deposit	Currency	EUR 2 USD
Uplata	MONTEGREEN S.R.L.	03.01.11.	INV 04011110 MONTEGREEN	1101031119304603	0.00	2,099.00	EUR
Uplata	MONTEVERDE DI NAVIGAZIONE S.P	03.01.11.	INV 1010+1110 MONTEVERDE	1101031119304604	0.00	3,805.00	EUR
Uplata	Macon d.o.o	03.01.11.	Invoice 11-10	1101031119551656	3,013.00	0.00	EUR

sheet 5 = EUR/USD history currency rate

Date	EUR	USD
01.01.11.	1	1.3362
02.01.11.	1	1.3362
03.01.11.	1	1.3348

Main currency is USD.

In EUR sheet in last column EUR 2 USD I would like to convert euro to usd using dates from eur sheet 2 and sheet 5 with and count conversion rate on specific date.
Should I use lookup formula or....

 

[Saba Sabaratnam]

Hi

Yes use the following Vlookup formula to convert EUR to USD:

=VLOOKUP(C3,Sheet5!A:C,3,FALSE)*G3

sheet 2 =EUR,
Info	Name	Date	Info2	Transaction	Debit	Deposit	Currency	EUR 2 USD
Uplata	MONTEGREEN S.R.L.	03.01.11.	INV 04011110 MONTEGREEN	1.1E+15​	0​	2,099.00​	EUR	2801.745​
Uplata	MONTEVERDE DI NAVIGAZIONE S.P	03.01.11.	INV 1010+1110 MONTEVERDE	1.1E+15​	0​	3,805.00​	EUR	5078.914​