In a continuation of yesterday's Podcast [Episode #1658], Bill looks closer at the Currency Conversion Table sent in by Bob. Going with a bit more advanced Excel solution, Bill employs a VLOOKUP to provide the conversion. Follow along with Episode #1659 to see a slightly different take - using a single VLOOKUP Formula.
"VLOOKUP Awesome Quick: From Your First VLOOKUP to Becoming a VLOOKUP Guru" by Bill 'MrExcel' Jelen. From 'MrExcel Labs', this Enhanced eBook is designed *exclusively for the iPad*. This e-publication Includes text, audio, and video; a media-rich and in-depth look at the Excel VLOOKUP by Bill Jelen. To read more, click here: VLOOKUP Awesome Quick
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
"VLOOKUP Awesome Quick: From Your First VLOOKUP to Becoming a VLOOKUP Guru" by Bill 'MrExcel' Jelen. From 'MrExcel Labs', this Enhanced eBook is designed *exclusively for the iPad*. This e-publication Includes text, audio, and video; a media-rich and in-depth look at the Excel VLOOKUP by Bill Jelen. To read more, click here: VLOOKUP Awesome Quick
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
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.
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.
