Look Up FX Rate by Transaction date

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
)