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?

    Your help is much appreciated!


Participate now!

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