using hlookup & vlookup as part of a formula

[sukai]

Hi,
can someone help me with the following?
I want to use a formula that will lookup the exchange rate for me for a currency in a month . What I actually want is the cell reference. Here is an example of what I am trying to do:

=AUD!C3/EX_Rates!G15+CAD!C3/EX_Rates!C15+CHF!C3/EX_Rates!F15+EUR!C4/EX_Rates!I15+USD!C6/EX_Rates!B15

CURRENCY EXCHANGE RATEs

US$ C$ DKr SKr CHF A$ NZ$ EURO HK$ NKR SGD
Mar-11 1.6138 1.5776 8.5795 10.2375 1.4810 1.5978 2.1795 1.1505 12.57528 9.02918 2.04585
Apr-11 1.6394 1.5679 8.4434 10.1191 1.4704 1.5427 2.0720 1.1322 12.73960 8.82100 2.0390
May-11 1.6321 1.5831 8.5202 10.2405 1.4245 1.5292 2.0455 1.1427 12.68240 8.96390 2.0203
Jun-11 1.6181 1.585875 8.40995 10.277275 1.360725 1.5272 1.988325 1.1277 12.5978 8.83645 1.997875
Jul-11 1.61886 1.55 8.42888 10.31282 1.32952 1.50042 1.90854 1.32952 12.6087 8.80184 1.96838
Aug-11 1.6365 1.6146 8.5267 10.5184 1.2816 1.5682 1.9678 1.1447 12.76220 8.95750 1.9818
Sep-11 1.5782 1.5865 8.5603 10.4909 1.3728 1.5479 1.9518 1.1497 12.29710 8.88390 1.9781

 

[Andrew Poulsom]

Welcome to MrExcel.

What range does you table occupy and where are the two fields that you want to look up? Maybe you can adapt this:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	CURRENCY EXCHANGE RATEs												May-11
2		US$	C$	DKr	SKr	CHF	A$	NZ$	EURO	HK$	NKR	SGD	CHF
3	Mar-11	1.6138	1.5776	8.5795	10.2375	1.481	1.5978	2.1795	1.1505	12.57528	9.02918	2.04585	1.4245
4	Apr-11	1.6394	1.5679	8.4434	10.1191	1.4704	1.5427	2.072	1.1322	12.7396	8.821	2.039
5	May-11	1.6321	1.5831	8.5202	10.2405	1.4245	1.5292	2.0455	1.1427	12.6824	8.9639	2.0203
6	Jun-11	1.6181	1.585875	8.40995	10.27728	1.360725	1.5272	1.988325	1.1277	12.5978	8.83645	1.997875
7	Jul-11	1.61886	1.55	8.42888	10.31282	1.32952	1.50042	1.90854	1.32952	12.6087	8.80184	1.96838
8	Aug-11	1.6365	1.6146	8.5267	10.5184	1.2816	1.5682	1.9678	1.1447	12.7622	8.9575	1.9818
9	Sep-11	1.5782	1.5865	8.5603	10.4909	1.3728	1.5479	1.9518	1.1497	12.2971	8.8839	1.9781
Sheet5

Cell Formulas
Range	Formula
M3	=INDEX(B3:L9,MATCH(M1,A3:A9,FALSE),MATCH(M2,B2:L2,FALSE))

 

[sukai]

A3 to L34

 

[Andrew Poulsom]

Did you try to adapt the formula I posted?

 

[sukai]

sorry. here is a more complete 4reply to your questions:

Range: A3:L3
Currency titles: B3:L3
Months: A4:A34

 

[Andrew Poulsom]

Still incomplete, sorry. Where are the two fields that you want to look up?

 

[sukai]

yes, I did. but I want too use it as part of the following formula

=AUD!C3/EX_Rates!G15+CAD!C3/EX_Rates!C15+CHF!C3/EX_Rates!F15+EUR!C4/EX_Rates!I15+USD!C6/EX_Rates!B15
Where I have the =AUD!C3/EX_Rates!G15 I want it look up the exchange rate for the respective currency for me for the appropriate month.

 

[sukai]

I don't think that I am explaining myself very well.
the 2 fields would be :
Currency titles: B3:L3
Months: A4:A34

 

[Andrew Poulsom]

I know what you want to do, but you still haven't told me where the month (eg May-11) and currency (eg CHF) are. In my example they are in M1 and M2.

 

[sukai]

Hi,
May 11 is in cell A6
CHF is in cell F3
All currency titles are in the range B:L3
the months are in the range A4:A34

CURRENCY EXCHANGE RATEs

US$ C$ DKr SKr CHF A$ NZ$ EURO HK$ NKR SGD
Mar-11 1.6138 1.5776 8.5795 10.2375 1.4810 1.5978 2.1795 1.1505 12.57528 9.02918 2.04585
Apr-11 1.6394 1.5679 8.4434 10.1191 1.4704 1.5427 2.0720 1.1322 12.73960 8.82100 2.0390
May-11 1.6321 1.5831 8.5202 10.2405 1.4245 1.5292 2.0455 1.1427 12.68240 8.96390 2.0203
Jun-11 1.6181 1.585875 8.40995 10.277275 1.360725 1.5272 1.988325 1.1277 12.5978 8.83645 1.997875
Jul-11 1.61886 1.55 8.42888 10.31282 1.32952 1.50042 1.90854 1.32952 12.6087 8.80184 1.96838
Aug-11 1.6365 1.6146 8.5267 10.5184 1.2816 1.5682 1.9678 1.1447 12.76220 8.95750 1.9818
Sep-11 1.5782 1.5865 8.5603 10.4909 1.3728 1.5479 1.9518 1.1497 12.29710 8.88390 1.9781
Oct-11 1.5859 1.6016 8.5550 10.4623 1.4068 1.5532 1.9936 1.1490 12.3400 8.9218 2.0093
Nov-11 1.5845 1.6247 8.6575 10.6893 1.4397 1.5682 2.0598 1.1675 12.3312 9.0970 2.0444
Dec-11 1.5591 1.594 8.8001 10.6456 1.4505 1.5338 2.0153 1.1838 12.123 9.1745 2.0178
Jan-12 1.5472 1.5691 8.8685 10.6320 1.4395 1.4741 1.9347 1.1930 12.1489 9.1351 1.9637
Feb-12 1.5806 1.5774 8.8794 10.5334 1.4420 1.4746 1.8958 1.9300 12.1489 9.1351 1.9909
Mar-12 1.5814 1.5681 8.9146 10.6639 1.4458 1.4916 1.9171 1.1990 12.2752 9.0185 2.0030
Apr-12 1.6016 1.5909 9.0345 10.7738 1.4598 1.5442 1.9519 1.2144 12.4318 9.2089 2.0110
May-12 1.5925 1.6072 9.2324 11.1910 1.4918 1.5969 2.0592 1.2420 12.3632 9.4124 2.0008
Jun-12 1.5501 1.5977 9.1931 10.9879 1.4855 1.5619 1.9988 1.2370 12.0266 9.3326 1.9870
Jul-12 1.5618 1.5830 9.4031 10.8509 1.5182 1.5160 1.9500 1.2642 12.1132 9.4578 1.9612
Aug-12 1.5733 1.5601 9.4367 10.4795 1.5221 1.5051 1.9414 1.2673 12.2036 9.2692 1.9813
Sep-12 1.6146 1.5785 9.3241 10.6548 1.5145 1.5479 1.9614 1.2509 12.5191 9.2726 1.9809
Oct-12 1.6055 1.5846 9.2377 10.6905 1.4986 1.5549 1.9607 1.2394 12.4470 9.2039 1.9654
Nov-12 1.5979 1.5927 9.2768 10.6998 1.4992 1.5374 1.9523 1.2437 12.3845 9.1334 1.9560
Dec-12 1.6120 1.5981 9.1748 10.6390 1.4853 1.5411 1.9425 1.2298 12.4932 9.0492 1.9759
Jan-13 1.5806 1.5943 8.7533 10.1885 1.4591 1.5142 1.8846 1.1730 12.2558 8.7244 1.9510
Feb-13 1.5266 1.5630 8.6527 9.8292 1.4221 1.4814 1.8277 1.1599 11.8400 8.6687 1.8893
Mar-13 1.5185 1.5427 8.8154 9.8730 1.4379 1.4565 1.8120 1.1825 11.7872 8.8563 1.8838
Apr-13 1.5492 1.5478 8.8668 10.1756 1.4589 1.5060 1.8190 1.1892 12.0275 9.0630 1.9155
May-13 1.5161 1.5670 8.7222 10.0775 1.4549 1.5814 1.9013 1.1699 11.7692 8.9229 1.9174
Jun-13 1.5167 1.6001 8.7031 10.2369 1.4426 1.6570 1.9639 1.1668 11.7641 9.2611 1.9236
Jul-13 1.5371 1.5823 8.6403 9.9481 1.4320 1.6647 1.9063 1.1588 11.9225 9.0895 1.9442
Aug-13 1.5469 1.6318 8.7513 10.2686 1.4423 1.7368 1.9997 1.1732 11.9953 9.4802 1.9750
Sep-13 1.5866