MrExcel podcast is sponsored by
Easy-XL. Learn Excel from MrExcel podcast,
episode: 1659, Convert Various Currencies. Hey! Welcome back to the MrExcel netcast. I’m Bill Jelen. Yesterday,
we answered a question from Bob. Bob was trying to Auto Filter a table and put in different values and
the formula AUTOCOMPLETE was or the AUTOCOMPLETE for the table
was driving him crazy. You know, a better way to go completely better way to go. First of all, let’s make this into a table
again with Ctrl+T. Click OK. That way our
AUTOCOMPLETE will work. We already had this little table up here that converts GBP to,
this is the conversion rate. We have to multiply the currency amount
by this to convert to GBP. So we can add a new column here GBP for Great Britain Pounds, all right. Equal, this amount, times the VLOOKUP. VLOOKUP stands for Vertical Lookup. WE Wanna look up that currency code,
comma, in this table here and I want to lock that down. So I’m going to press F4, comma. We want the second column, So two and we want exact matches, so we’re going to put a False at the end. All right, so the amount times
the rate here in the lookup table. We’re getting the second column
from the lookup table. I’ll press Enter
and we actually want Excel to copy that down automatically. And so it’s just a little test here. €2083, comes up with 1799. I do equal 2083, times .864. 1799, all right, so there’s a single formula that gets copied down and
solves the problem, all the way down. Yeah, as I look at this point 408, I’m going
to add one more thing in here. I bet we want to round this off
to the nearest either dollar or whatever there, I don’t know!
Is it’s cents? I don’t know. and we will copy that formula down. All right, so there we go.
I’ve the single formula that will go out to this lookup table, figure out the currency conversion rate and then tomorrow you know,
if the rates change, if I have to do this some other day, I just come here and you know,
update the new value like .62 and you
see that the relevant cells, The relevant cells
will automatically update So this VLOOKUP formula,
a little bit more straightforward than using the filter
in four different formulas that Bob was trying to do yesterday
but I understand. Yeah! There’s five ways
to skin a cat in Excel and sometimes you know,
VLOOKUP is eluding you. So the the other way
certainly, would’ve worked All right! Hey, I wanna thank you for stopping by. We’ll see you next time
for another netcast from MrExcel.