Myujth

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!

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() :)

You can use INDEX with MATCH to achieve this as well and wrap in IFERROR in case a match for the "date" string is not found in the lookup column. If a match is found in the lookup column E, for the "date" string in column A the number returned for the match is passed as a row number argument for Index on column F which returns to rate in the same row as the match was found. This is then multiplied by column B.

You would alter the ranges $F$2:$F$3 and $E$2:$E$3 to encompass all your actual rows in those columns.

In B2 and drag down

=IFERROR(B2*INDEX($F$2:$F$3,MATCH(A2,$E$2:$E$3,0)),"")