Forum Discussion

Tarig Elsawi's avatar
Tarig Elsawi
Copper Contributor
Sep 08, 2023
Solved

If conditional multiplication

Hi, i have a table containing exchange rate and I would like excel to multiply cells based on currency symbol on each cell. for example, if the cell is £150 then excel should lookup £ exchange from...
Formulas and Functions
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Sep 09, 2023

    Tarig Elsawi 

    Depending upon the situation, one possible solution is to write a named Lambda function

    ExtractCurrencyFormatλ

= LAMBDA(ref,
    LET(
      rawFormat, GET.CELL(7, ref), 
      TEXTBEFORE(TEXTAFTER(rawFormat, "["), "]")
    )
  )

    The worksheet formula would be 

    = ExtractCurrencyFormatλ(currencyAmount)
for a single value, or
= MAP(currencyAmount, ExtractCurrencyFormatλ)
for an array.

     

    Reference:

    How to find the currency format in a formula | MrExcel Message Board

Tarig Elsawi's avatar
Tarig Elsawi
Copper Contributor
Sep 09, 2023
It’s formatting
HansVogelaar's avatar
HansVogelaar
MVP
Sep 09, 2023

Tarig Elsawi 

I;d use a separate column for the currency. You can then use a simple INDEX/MATCH formula instead of making Excel jump through hoops to determine the number format.

See the attached demo.

Resources