Convert multiple currencies to one in Excel

[dehm83]

Hello All,</SPAN>

I’m a long term lurker, first time poster. I never felt the need to register before as I had always found the answers to my questions until now!</SPAN>

I am trying to convert multiple currencies in excel to CAD using the VLOOKUP function, but I keep returning a <st1:stockticker>REF</st1:stockticker># error.</SPAN>

I have a sheet called [XRate] with the following (with 170 data rows)</SPAN>

A B</SPAN></SPAN>
Currency CAD per Unit</SPAN></SPAN>
1 USD 0.956</SPAN>
2</SPAN> ANG 0.534</SPAN>
3 </SPAN> <st1:stockticker>AUD</st1:stockticker> 1.007</SPAN>


The destination sheet looks like this:</SPAN>

E F G</SPAN></SPAN>
Total </SPAN>Lot</SPAN> Value Currency Currency in CAD</SPAN></SPAN>
1</SPAN> 130.00 <st1:stockticker>AUD</st1:stockticker> ?</SPAN>
2 </SPAN> 1450.00 USD ?</SPAN>
3</SPAN> 45.92 ANG ?</SPAN>


My formula looks like this so far:</SPAN>

=SUM(VLOOKUP(F1,'XRAte’!!$A$1:$A$170,2,FALSE)*E1)</SPAN>


Am I way off?</SPAN>

Your help is appreciated!</SPAN>

 

[VoG]

Try

=VLOOKUP(F1,'XRAte’!$A$1:$B$170,2,FALSE)*E1

 

[Dryver14]

You may need to look up G1 as F1 seems to have to look up G1 as this seems to be the column with the curreny and F1 holds the amount.

=F1*(VLOOKUP(G1,'XRAte’!$A$1:$B$170,2,FALSE)*E1)

Not 100% sure of the syntax on this one....not tried.

 

[mooseman]

Is the sheet named XRAte or XRate?

 

[dehm83]

Sucess! Thanks so much!