Look Up FX Rate by Transaction date

Hello Folks,

Attached you will find a link for 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.