Currency exchange using VLOOKUP

[BlueLobster]

I have problems converting currency , anyone know how to make use of VLOOKUP to convert currency ?

[daddylonglegs]

What currency are you converting from/to? Are there multiple possibilities or are you always converting from/to a specific currency?

If you are alway converting from £ to another currency, for example, then you could have a table which contains currencies in the first column and the exchange rate in the next, e.g. currencies in Y2:Y10 and exchange rate in Z2:Z10

Now with a specific currency in A2 and amount in B2 this formula in C2 will convert

=LOOKUP(A2,Y$2:Z$10,2,0)*B2

You may have to change *B2 to /B2 depending on how your exchange rates are expressed

[BlueLobster]

i'm converting MYR and THB to SGD.

there will only be this 3 currency and it is always converting myr and thb to sgd.

side note ,

i have 2 tables , 1 is showing the currency in myr and thb , the other showing all in sgd .

sgd 1 = myr 2.5

sgd 1 = thb 23

Thanks!

[BlueLobster]

is it possible to use VLOOKUP rather than lookup .

[daddylonglegs]

Sorry I mistyped, the function should be VLOOKUP, i.e.

=VLOOKUP(A2,Y$2:Z$10,2,0)*B2

[BlueLobster]

it works , but i need to do it another way.

i have to group MYR as CODE 1 , and THB as CODE 2 .

my VLOOKUP is

CODE 1 - EXCHANGE RATE
CODE 2 - EXCHANGE RATE

base on the table, how do you do ?

[BlueLobster]

i found a way to do it ,

=VLOOKUP(B5,$H$6:$I$7,2)*B6

B5 as the code.

when i do absolute addressing ,

=VLOOKUP(B5,$H$6:$I$7,2)*B6

=VLOOKUP(B6,$H$6:$I$7,2)*B7

the b5 changes to b6. how do i keep it as b5 thru out ?

[daddylonglegs]

You can use the $ sign to "fix" a cell reference (make it absolute) so if you dont want the 5 to change as you copy down use B$5, If you don't want the B to change as you copy across use $B5. If you want neither to change use $B$5

[BlueLobster]

thanks for your help! do you have msn? so i can get help from you easier ?

[daddylonglegs]

Best to just post your question here (in a new thread of course) - there are many contributors here who will be able to help you equally as well if not better than me.

[BlueLobster]

alright , i have another question . after i convert my figure. the figure is like $24,567.

how do i apply the formula of rounding to make it $25,000. (ROUND UP)

and how to make values like $24,300 to $24,000. (ROUND DOWN)

how do i apply both the (IF) and (rounding) formulas ?