Excel formula for adding correct exchange rate to correct cell

[adminragnar]

Hello,

May I ask for help with this please.

I am running a web store, and in columns A-C (marked as 1) you can see parts of the sales report (see screenshot).

In column D, I wish to automatically insert the correct currency exchange rate for the Purchase Date (column B) and for the currency that was used to pay the order (column C).

Columns F-H and J-L (marked as 2 and 3) contains the exchange rates that should be used for Euro and USD.

So to get the correct currency exchange rates into the different rows of column D, the formula needs to:

-check that the order date (column B) is the same as the date in column F or J.

-check that the order currency (column C) is the same as the currency in column G or K.

For example:

-In cell D2, the correct currency exchange rate to be inserted is 10,35498 (from cell L10), because the order date (column B) is 2024-09-11 and the currency (column C) is USD.

Thank you!

[#2 · Accepted solution]

@adminragnar 

=IF(C2="USD",INDEX($L$2:$L$16,MATCH(B2,$J$2:$J$16,0)),INDEX($H$2:$H$16,MATCH(B2,$F$2:$F$16,0)))

You can use IF along with INDEX and MATCH.

[#3]

@OliverScheurich Perfect, thank you very much!

[#4]

@adminragnar 

I'd only use 1 instead of 0 as third parameter in MATCH for @OliverScheurich  solution. Exchange rate skips non-trading dates. Look, you have exchange rate for Aug 30 and next for Sep 2. Since Aug 31 and Sep 01 are on weekend. 

However, order could be date on last date of the month, i.e. Aug 31. If so with parameter 0 you have an error.  With parameter 1 you have exchange rate for the last previous date, i.e. for Aug 30. Which is correct, exchange rates for not-trading dates are the same as for last trading date.

[#5]

@SergeiBaklan Thank you!

[#6]

@SergeiBaklan and @OliverScheurich

If I wish to add a third currency, my domestic SEK, as shown in the screenshot columns N-P, how would I write the formula?

Thank you!

[#7]

@adminragnar 

On which Excel platform/version you are? Is that Excel365, 2021 or earlier one?

[#8]

@adminragnar 

Okay, on any Excel that could be

=INDEX( INDEX($F$2:$Q$1000, 0, MATCH(C2, $F$2:$Q$2, 0)+1 ), MATCH(B2, INDEX($F$2:$Q$1000, 0, MATCH(C2, $F$2:$Q$2, 0)-1 ), 1 ) )

[#9]

@SergeiBaklan Thank you very much!

[#10]

@adminragnar 

An INDEX variant using the seldom used reference arrangement:

=LET(
    i, XMATCH(dates, EUR[date]),
    a, XMATCH(currency, {"EUR", "USD", "SEK"}),
    INDEX((EUR, USD, SEK), i, 3, a)
)

[#11]

@Patrick2788 Thank you!