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...
  • PeterBartholomew1's avatar
    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

Resources