V-Lookup and If Equations

• I have a Date (Column A), a Currency (Column B) and a few Values (Columns C, D, E). Imagine in my real file, there are hundreds of rows with values in Column A, B, C, D, E, however I have attached a sample data set here so there are only four rows. I am trying to convert the values in Column C, D and E into USD. In Column K and L, I have a date and the exchange rate from GBP to USD. In the example I only have a few days, but in reality I have a row for each day over the past twenty years. Similarly, I have the same thing in Column M and N but for Euros.

I need assistance in creating a formula for the cells in F, G and H that say if the value in Column B is GBP, do a Vlookup on the date in Column A to multiply the value in Column C, D and E by the exchange rate on that day (Column K + L or Column M + N).

So, for example, as B2 is GBP and A2 is 2/1/17, F2 will find the date in Column K that matches A2 and then multiple the value in C2 by the exchange rate in Column L that corresponds to the correct date.

It seems I need to create a Vlookup for each currency and do an If equation based on the currency value in Column B to dictate which Vlookup to utilize in each row?

forum.ozgrid.com/index.php?attachment/71281/

Files

• Re: V-Lookup and If Equations

try below formula

=IF(\$B2="GBP",VLOOKUP(\$A2,\$K:\$L,2,FALSE),VLOOKUP(\$A2,\$M:\$N,2,FALSE))*C2

• Re: V-Lookup and If Equations

Perfect, thanks!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!