Lookup, Vlookup, Xlookup???

[MrDB4Excel]

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.

 

[Jeffrey Mahoney]

Vlookup is the tool of choice because it's backward compatible if you share with others without the latest version of Excel.

With VLookup you need to specify that you are looking for an approximate match, because, I'm assuming you don't have rates downloaded for every day. This method would default to the last date that is equal to or less than the date provided.

Were you just trying to decide which of the functions to use, or did you need more help with the function?

Jeff

 

[MrDB4Excel]

Thanks Jeff, but I do download rates every day. The image I sent is from the file CurrencyConversionRates.xlsm. As you can see in this next image: CurrencyConversionRatesTabNames I have been tracking exchange rates daily since late 2015 (October) but every day beginning January 2016. So yes I need help writing a formula that looks at the given date I enter in the aforementioned cells in the file that contains what is shown in the image Dollar2CrownPerExchangeRate. This file is called ActualMonthlyUsage.xlsx and is where I keep track of monthly living expenses. The relevant tab in this file is called Date Of $ Acct Chges and is the tab I used to create the image Dollar2CrownPerExchangeRate. I need to look up the exchange rate in the file CurrencyConversionRates.xlsm per the date entered next to the dollar amount entered in columns C & G as I said in my first post. This is the part I am very uneducated about and need help with. It all could be clearer if I was allowed to upload the actual excel files, then you could see it better. I hope this helps.

 

[Jeffrey Mahoney]

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.

 

[MrDB4Excel]

Jeff, by the year, is how I have done it because of other excel files besides the files indicated in this forum thread. USD to CZK is the only exchange rate I need to get per the date in question. I am the only user for all these files. The main reason I am now asking about this is today after perusing the ActualMonthlyUsage.xlsx file and specifically the tab Date Of $ Acct Chges I noticed some mistakes and then had to look through all 2019 bank statements and the CurrencyConversionRates.xlsm & 2015_CSOB-USD.xlsm files to make corrections. All entries in the tab Date Of $ Acct Chges up to this point have been manual, so this is why I thought of using some lookup formula to insure correct data input.
I think if you saw my entire lineup of excel files I use daily to keep track of then either you would understand my system or you might propose something better. Some of these files, however, are used for personal financial record keeping, thus I would not want those to be seen. This list will help you see the lengths I go to for record-keeping and there is more but not for financial records.
p.s. is there a way to upload excel files?
2015_CSOB-CZK.xlsm
2015_CSOB-USD.xlsm
ActualMonthlyUsage.xlsx
Annual Building Fee Statement.xlsx
Annual-Financial-Overview.xlsx
CurrencyConversionRates.xlsm
Marianky Bus Usage.xlsx
PensionReceiptHistory.xlsx
RealPensionProjection.xlsx
TotalCurrentAssets.xlsx

 

[sandy666]

did you think about Power Query for your whole structure? this is an idea only

 

[Jeffrey Mahoney]

Yeah, you should keep your financial records to yourself. I know it's hard sometimes to make a generic version for people to see. There is no way to attach files. You can share a link to a Google Drive, or Dropbox.

 

[MrDB4Excel]

did you think about Power Query for your whole structure? this is an idea only

Sandy666, thanks, I am aware of Power Query, but do not know enough to put all these files into such a structure.

 

[MrDB4Excel]

Yeah, you should keep your financial records to yourself. I know it's hard sometimes to make a generic version for people to see. There is no way to attach files. You can share a link to a Google Drive, or Dropbox.

Jeff, thanks for your help. I am beginning to think I will keep doing as I have been doing until I can learn enough about lookup, or as Sandy666 suggested using Power Query to make the leap. If you have a simple lookup formula in mind I will try that.
Again, thank you so much for your time.
1 am here in the Czech Republic, need to get some sleep, brain is foggy.

 

[sandy666]

sure, maybe for future:

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference