formula required to calculate the total amount

[Bandc]

I am unable to apply my nested if else formula.
I required to calculate the total amount driven by the exchange rate. Exchange rate will be picked by date and currency symbol. please see the attached excel and kindly suggest the appropriate formula to apply..

Thanks in advance for your kind help.

 

[pecoflyer]

In H15 and pulled down =INDEX($F$3:$I$8,MATCH($F15,$F$3:$F$8,1),MATCH(LEFT($G15,3),$F$3:$I$3,0))
To calculate the next column, formula varies depending on your regional settings

 

[Bandc]

Hi Pecoflyer, Thank you so much it works.
Can we drive it by defining name? (formulas, Name Manager)

Define F4:I9 and give them a name "exc_rate"
Is it possible?

 

[pecoflyer]

Define F4:I9 and give them a name "exc_rate"
Is it possible?

Should work without problem. You van even make it an Excel table to provide for added currencies and refer to the table instead of a named range

 

[Bandc]

Oh I See But I never did this with table in past. I am the beginner.
If you can help.. It would be great.

 

[Peter Bartholomew]

There are various levels at which one can introduce Names. Are you an Excel 365 beginner or do you use a legacy Excel version?
Using Tables to hold input data that are prone to change is good practice and one that provides structured references. With older versions of Excel you might then use the order table for calculation.

= INDEX(ForExRate,
    MATCH([@Date], ForExRate[Date]),
    MATCH(LEFT([@Amount],3), ForExRate[#Headers], 0))

With 365 and dynamic arrays, it is often better to perform the calculation outside the table as arrays. With the LET function, one can also introduce Names at formula level.

= LET(
    amount, REPLACE(Orders[Amount],1,4,""),
    symbol, LEFT(Orders[Amount], 3),
    exRate, INDEX( Rate,
      MATCH(Orders[Date], ForExRate[Date]),
      MATCH(symbol, Currency, 0)),
    amount*exRate
  )

 

[Bandc]

Thank you for your kind help, I am Excel 365 beginner.

 

[Peter Bartholomew]

In that case the formulas should work for you. That is not the same as knowing how they work or being able to reuse the methods.
The functionality I have relied upon includes:
1. Tables and Structured References
2. Dynamic Arrays
3. The LET functions and names local to the formula
Balanced against that, one can forget about many of the 'tips and tricks' that go into traditional spreadsheet development.

 

[pecoflyer]

Oh I See But I never did this with table in past. I am the beginner.
If you can help.. It would be great.

The purpose of forums is not only to help solve problems, but also to help posters learn Excel so they can solve them by themselves in the future, therefore saving their time. If you follow the provided link its is all clearly explained.