Forum Discussion

ShamsM's avatar
ShamsM
Copper Contributor
Nov 14, 2025

Look Up FX Rate by Transaction date

Hello Folks,

Attached you will find a sample file where I am trying to do the following:

 

https://docs.google.com/spreadsheets/d/1NeCSt8oAcEM86DAxI-8LkqTQK4D6WQvv/edit?usp=sharing&ouid=103354753371375324640&rtpof=true&sd=true

 

  • Under the Data tab, if B2="C", then C2/xlookup(A2/FX Rate Oct 2025), C2)

basically, if the Currency Code is C for Canadian then divide the Canadian $ on that transaction date by the reported FX Rate for that month that is stored in the FX rate tab. Otherwise, just return the contents of that cell.

I tried to do a xlookup by the transaction date but since the data file is converted from a flat file template, I think my lookup function is not recognizing the lookup field between the array and the cell reference. I am more than happy to create a helper column for the Transaction date that will match the format of the month in the FX rate tab.

Hope you can point out a clean solution for this approach. Thank you.

Regards,

Shams.

excel

1 Reply

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Nov 17, 2025

    Hi

    Expect if I missed something you're only challenge is to find/match the appropriate [Period] in the 'FX Rate' tab according to your [Transaction date]
    In the 'Data' tab the [Transaction date] can be any date in a month but in the 'FX Rate' tab the [Period] consist of only one date per month

    So, before any lookup you should "convert" your Transaction date to the begin. of the corresponding month. Something you can do with:

    =EOMONTH( [Transaction date], -1) +1

    then xlookup this date in your [Period] range:

    in G2 above:

    =IF( B2 = "C",
  C2 / XLOOKUP( EOMONTH(A2, -1) +1, 'FX Rate'!A$2:A$13, 'FX Rate'!B$2:B$13, "No match" ),
  C2
)

     

Resources