Excel database - formula multiplication exchange rate by date

Question

I am creating some accounting papers and I need multiplicate USD exchange rate with our currency by date. I tried everything, but I don't know how to do it.. Here is example:

DATE       |    USD  | CZK

1.1.2018 | 2$ |  USD Price * CZK Price by same date

2.2.2018 | 2$ |  USD Price * CZK Price by same date


EXCHANGE RATE

1.1.2018 | 22

2.2.2018 | 23

(It means that price on 1.1 will be 44CZK and 2.2 will be 46CZK)

And this I need do for every day in year. So hand writing isn't possible. I need some formula for it. Can you help me please? I know that it can be by vlookup and If..

Thanks!

Answer 1

Yes, you can muliply with the exchange rate in your cell while doing the lookup at the same time, so in Cell C2:

=B2*(VLOOKUP(A2,$E$2:$F$3,2,FALSE))

---

I.e. VLOOKUP(A2,$E$2:$F$3,2,FALSE) will give you the exchange rate,

A2: Lookup value, the date in our case.

$E$2:$F$3: Where we can find the date in your "search area". Notice that the date we search for, needs to be in the first column of our "search area".

2: In our "search area", from which column number should we return our return number/value. In our case our "search area" is two column, where we want the result to be return from the 2nd column of column E and F.

FALSE: Search for exact match.

When the exchange rate is found we mulptiply it with the dollar amount, i.e. B2 * Vlookup() :)