Date and currency look up

[PM1]

I tried to tackle this a few months ago and had to drop it as I didnt have Excel Jeanie, now I have

I need to look up a Currency in a table and return the value at the date the entry was made.

So this table is a record of all purchases:-

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	PO Date	Budget Yr	LPO No	Description	Supplier	Qty	Local Curr	Unit Cost Local Curency	Ext Cost Local Currency	Ext Cost Euro	Ext Cost USD
2143	Jan-14	2014	2439	Wooden Cabinets & Shelves 3rd Floor	Japan Construction & Trading	1	BHD	985.00	985.000	2,159.018	2,612.628
2144	Jan-14	2014	2439	Demolition of walls between Vault Room & Corridor	Japan Construction & Trading	1	BHD	190.00	190.000	416.460	503.959
2145	Jan-14	2014	2439	Refixing of Ceiling & Flooring	Japan Construction & Trading	1	BHD	85.00	85.000	186.311	225.455
2146	Jan-14	2014	2440	Daily Pre Paid Package for BCP Testing at BNPP DOHA	Progresssoft	2	USD	1,500.00	3,000.000	2,479.134	3,000.000
2147	Jan-14	2014	2441	Miocrosoft Wireless 3000 Keyboard	HBC Printer Supplies Llc	2	AED	240.00	480.000	107.992	130.682
2148	Jan-14	2014	2442	Maintenance of FileNet Scanners	Cad Gulf Llc	1	AED	54,500.00	54,500.000	12,261.635	14,837.805
2149	Jan-14	2014	2443	Maintenance Contract for Etrali Bahrain - Jan 1 - Dec 31	ETRALI	1	EUR	23,170.00	23,170.000	23,170.000	28,038.017
2150	Jan-14	2014	2443	Maintenance Contract for Etrali Dubai - Jan 1 - Dec 31	ETRALI	1	EUR	10,377.00	10,377.000	10,377.000	12,557.208
2151	Jan-14	2014	2444	KVM Cables Cat5 KVM USB	Hewlett Packard (HP)	5	ZAR	994.36	4,971.800	355.214	429.845
2152	Jan-14	2014	2445	Dry Contact for Touch Panel	Cotton House for Quilts	5	BHD	125.00	625.000	1,369.936	1,657.759
2153	Jan-14	2014	2446	5M LC-LC Multimode Cable	Peripherals And Parts Trading Llc	30	AED	45.00	1,350.000	303.729	367.542
LPO Master Data

This is my currency table for 2014

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
4	Code	Description	Average	January	February	March	April	May	June	July	August	September	October	November	December
6	AED	DIRHAM DES E.A.U.	4.851560877	4.95359147	5.07718791	5.05999369	5.09511625	5.00770004	5.02916505	4.91747935	4.82418388	4.63801186	4.60153441	4.57000882	4.44475781
23	BHD	DINAR DE BAHREIN	0.497996374	0.50846802	0.52115475	0.51937587	0.52300908	0.51402225	0.51628087	0.50474099	0.49517807	0.4760756	0.47232439	0.46910073	0.456225854
36	CHF	FRANC SUISSE	1.212498595	1.2245742	1.21365941	1.21786729	1.22038921	1.22031118	1.21412409	1.21717003	1.20612806	1.20584773	1.20594529	1.20139953	1.202567135
57	EUR	EURO	1	1	1	1	1	1	1	1	1	1	1	1	1
62	GBP	LIVRE STERLING	0.802386448	0.82069617	0.82485978	0.82632039	0.82143599	0.81331468	0.80043258	0.79294007	0.79292442	0.77936185	0.7834162	0.7961097	0.776825553
93	KWD	DINAR DU KOWEIT	0.376307849	0.38139148	0.38905525	0.38789774	0.38989338	0.38425385	0.38576605	0.37894065	0.37387902	0.36404631	0.36336212	0.36272163	0.354486699
105	MAD	DIRHAM DU MAROC	11.15454532	11.1990551	11.2628429	11.2439727	11.2535228	11.2196928	11.2346965	11.1921012	11.149979	11.0665483	11.0376696	11.0134101	10.98105295
137	QAR	RIYAL DU QUATAR	4.809507424	4.911109	5.03371458	5.01639268	5.05058715	4.9627761	4.9850736	4.87457082	4.7823521	4.59848625	4.56213387	4.53013225	4.40676069
143	SAR	RIYAL D'ARABIE SAOUDITE	4.954612911	5.05804441	5.18410881	5.16648217	5.20262425	5.11349988	5.13555966	5.02110247	4.92584104	4.73739423	4.70019242	4.66954484	4.54096078
171	USD	DOLLAR US	1.320879167	1.34865	1.3823	1.3776	1.3872	1.3634	1.3693	1.3388	1.3134	1.2628	1.2528	1.2442	1.2101
191	ZAR	RAND COMMERCIAL AFRIQUE DU SUD	14.35131111	15.0634767	14.8500494	14.5006182	14.5947412	14.434044	14.5566862	14.3385483	13.9991368	14.2744391	13.84782525	13.75954607	13.9966221
Sheet2

I need to look up the currency code in the PO File, find it in the currency file then look at the date in the LPO file and ensure that the conversion is in that date period.

I convert Local to Euro then Euro to USD

 

[Andrew Poulsom]

Here's an example that you may be able to adapt (formula in C2 copied down):

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	PO Date	Local Curr	Rate		Code	Description	Average	01/01/2014	01/02/2014	01/03/2014	01/04/2014	01/05/2014	01/06/2014	01/07/2014	01/08/2014	01/09/2014	01/10/2014	01/11/2014	01/12/2014
2	01/01/2014	BHD	0.50846802		AED	DIRHAM DES E.A.U.	4.851560877	4.95359147	5.07718791	5.05999369	5.09511625	5.00770004	5.02916505	4.91747935	4.82418388	4.63801186	4.60153441	4.57000882	4.44475781
3	01/02/2014	CHF	1.21365941		BHD	DINAR DE BAHREIN	0.497996374	0.50846802	0.52115475	0.51937587	0.52300908	0.51402225	0.51628087	0.50474099	0.49517807	0.4760756	0.47232439	0.46910073	0.456225854
4	01/03/2014	AED	5.05999369		CHF	FRANC SUISSE	1.212498595	1.2245742	1.21365941	1.21786729	1.22038921	1.22031118	1.21412409	1.21717003	1.20612806	1.20584773	1.20594529	1.20139953	1.202567135
Sheet1

Cell Formulas
Range	Formula
C2	=INDEX(H$2:S$4,MATCH(B2,E$2:E$4,FALSE),MATCH(A2,H$1:S$1,FALSE))

 

[PM1]

Thanks for this,

One query I have is that the dates in the PO File that I have are not fixed to the first of each month. I need to keep the PO date and lookup the month month and year in the Currency Table as this will grown when I add 2015 rates into the table month by month and so on.

 

[Andrew Poulsom]

Here's another example:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	PO Date	Local Curr	Rate		Code	Description	Average	January	February	March	April	May	June	July	August	September	October	November	December
2	04/01/2014	BHD	0.50846802		AED	DIRHAM DES E.A.U.	4.851560877	4.95359147	5.07718791	5.05999369	5.09511625	5.00770004	5.02916505	4.91747935	4.82418388	4.63801186	4.60153441	4.57000882	4.44475781
3	16/02/2014	CHF	1.21365941		BHD	DINAR DE BAHREIN	0.497996374	0.50846802	0.52115475	0.51937587	0.52300908	0.51402225	0.51628087	0.50474099	0.49517807	0.4760756	0.47232439	0.46910073	0.456225854
4	21/03/2014	AED	5.05999369		CHF	FRANC SUISSE	1.212498595	1.2245742	1.21365941	1.21786729	1.22038921	1.22031118	1.21412409	1.21717003	1.20612806	1.20584773	1.20594529	1.20139953	1.202567135
Sheet1

Cell Formulas
Range	Formula
C2	=INDEX(H$2:S$4,MATCH(B2,E$2:E$4,FALSE),MATCH(TEXT(A2,"mmmm"),H$1:S$1,FALSE))

 

[PM1]

A2 is a date field so is H1:S1, so could I use Month instead?, tried but failed

INDEX(H$2:S$4,MATCH(B2,E$2:E$4,FALSE),MATCH(month(A2),H$1:S$1,FALSE))

 

[Andrew Poulsom]

What's in H1:S1 when formatted as dd/mm/yyyy?

 

[PM1]

Entered as 01/01/201? then cell formatted to MMM/YR to show:-

Jan 13, Jan 14, Feb 14, etc

 

[Andrew Poulsom]

In that case try:

=INDEX(H$2:S$4,MATCH(B2,E$2:E$4,FALSE),MATCH(DATE(YEAR(A2),MONTH(A2),1),H$1:S$1,FALSE))

 

[PM1]

Andrew, That worked great.

I added a little to Invert the rates and arrive at a local cost thus:-

=IF(ISNA(1/INDEX('Conv Rates'!$C$5:$AA$195,MATCH($G2,'Conv Rates'!$A$5:$A$195,FALSE),MATCH(DATE(YEAR($A2),MONTH($A2),1),'Conv Rates'!$C$4:$AA$4,FALSE))*$I2),0,1/INDEX('Conv Rates'!$C$5:$AA$195,MATCH($G2,'Conv Rates'!$A$5:$A$195,FALSE),MATCH(DATE(YEAR($A2),MONTH($A2),1),'Conv Rates'!$C$4:$AA$4,FALSE))*$I2)

Thanks again for your help and quick response, so glad to have experts like you avaialable.

 

[Andrew Poulsom]

If you have Excel 2007 or above you can use IFERROR rather than IF(ISNA:

=IFERROR(1/INDEX('Conv Rates'!$C$5:$AA$195,MATCH($G2,'Conv Rates'!$A$5:$A$195,FALSE),MATCH(DATE(YEAR($A2),MONTH($A2),1),'Conv Rates'!$C$4:$AA$4,FALSE))*$I2,0)