Lookup, Vlookup, Xlookup???

MrDB4Excel

Active Member
Joined
Jan 29, 2004
Messages
327
Office Version
  1. 2013
Platform
  1. Windows
I have a file that keeps track of a dollar debit and converts to a Kč (Koruna or crown) amount based on the exchange rate of the day the dollar amount was subtracted from my dollar account.
The row containing this data can be seen in the attached image.
I began this record-keeping in January of 2019 as you can see by the image. When the dollar debit is indicated by my dollar account statement then I post the amount, then the date of that amount and finally the USD to CZK exchange rate from that date. This exchange rate comes from a daily exchange rate file I keep as seen in the second image called CurrencyConversionRates.jpg.
What I would like to do is as follows:
When the dollar amounts are input (cells in column C & column G) then the Date is entered in the relevant cells in the Date Columns D & H causing a lookup formula to look at the relevant date in the CurrencyConversionRates file and return the USD to CZK exchange rate per that date.
I hope this is clear enough if not ask and I will provide whatever is needed to clarify.
Thanks for your help.
 

Attachments

  • Dollar2CrownPerExchangeRate.jpg
    Dollar2CrownPerExchangeRate.jpg
    39.5 KB · Views: 19
  • CurrencyConversionRates.jpg
    CurrencyConversionRates.jpg
    36.9 KB · Views: 19
The simple formula: =VLOOKUP(A1,CurrencyConversionRates.xlsm!Exchange_List,3,FALSE)
I created a named range called Exchange_List that encompasses the whole exchange table in your Exchange workbook.

I'm curious as to why you would split up your exchange data by year? Wouldn't it be easier to keep a running list? No formula changes.

Do you ever need to look up different exchange rates? I think you should incorporate a column where the user can select which exchange value they want to return. EUR to USD, C2K to USD, and so on. If you did that, we could create a formula that matches the exchange you need and pulls it from the right column. I would create a list and give it a named range and use it as a data validation list.
Hello Jeff,
I finally was able to bring a solution to this and it works perfectly, thank you for your help. Here is what I came with per your submission:
=VLOOKUP(D13,’F:\Finances\CSOB\[CurrencyConversionRates.xlsm]2019-ExchCalcs’!$A:$G,5,TRUE) D13 is the date of the $3 Pension Transfer Fee
and
=VLOOKUP(H13,’F:\Finances\CSOB\[CurrencyConversionRates.xlsm]2019-ExchCalcs’!$A:$G,5,TRUE) H13 is the date of the Account maintenance, statements, and transactions fee which varies month to month
Now all I do is input the $ amount and the date they occur and the formula does the rest. In addition, the only change to this formula year to year is the tab name. Next year, beginning January 2020, it will be 2020-ExchCalcs
 
Upvote 0

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Might I offer a small change of methods. Since you start a new tab each year and then have to change the formulas to look at that new tab, here's what I suggest. Name the tab "CurrentYear". At the end of the year, copy the sheet and rename it based on the year. Then update the data on the CurrentYear sheet. No formula changes needed.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top