If conditional multiplication

Copper Contributor


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 the exchange table and multiply by 150.

thank you

11 Replies

Does the "£" show along with the value in the formula bar or is it added through cell formatting?

It’s formatting

@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.

best response confirmed by Tarig Elsawi (Copper Contributor)

@Tarig Elsawi 

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


= LAMBDA(ref,
      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.



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

@Hans Vogelaar 

Hans,  the board has suddenly decided that I should not be allowed to post pictures.  Has any such thing happened to you or have I been singled out for demotion?

@Peter Bartholomew 

I guess @Hans Vogelaar has full functionality. Peter, I've seen you comment on MTC news, however, I'd suggest to involve @cuong .

@Peter Bartholomew 

It hasn't happened to me, but as MVP I (like @Sergei Baklan) probably have a higher permissions level than you.

@Tarig Elsawi 

You could use the CELL function to identify the cell format.





If £150 is A1, for example, the above returns ",2"

if $150 is A1, the above would return "C2"


Why you might not want to use CELL:

- it's slow

- it doesn't spill

and this:

Note: If the info_type argument in the CELL function is "format" and you later apply a different format to the referenced cell, you must recalculate the worksheet (press F9) to update the results of the CELL function.


@Peter Bartholomew 

It looks like it has to do with the new community ranks such as Silver Contributor - see What’s New in the MTC? September 2023 – Community Ranks 

I hope that the powers that be will correct this soon.

I understand this was a known issue that's been fixed. Please let us know otherwise.


Yes. All is good now, thanks.