Excel database - formula multiplication exchange rate by date

user_10633231 0
Viewed 953

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!

rate formula excel-formula excel
2 Answers

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)),"")

enter image description here

user_62412350

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

enter image description here

user_45525290
Related