In my Google Sheet table, I use SWITCH command to convert a cell from coin symbol -> into coin value.
what I currently do:
=SWITCH(D660,"₪","ILS","$","USD","Ft","HUF","€","EUR","лв","BGN","£","EGP")
this will convert cell D660 from coin symbol (₪/$/Ft/€/лв/£) -> into coin name
here’s an example of my data:
where in column E I have the equation above.
what I want to do:
I want to a more generic way, where I have a "hash-map" table in my Settings tab which contains the following table:
| coin_symbol | coin_name |
|---|---|
| ₪ | ILS |
| $ | USD |
| Ft | HUF |
| € | EUR |
| лв | BGN |
| £ | EGP |
and now I want the =SWITCH(D660,...) to use the table instead of hard coding inserting the conversion table
.
I’m struggling on this one, I tried stuffs like
=SWITCH(D660,A1:A10,B1:B10) or involving ARRAYFORMULA somewhere but nothing worked.
anyone have a suggestion how to implement that?
>Solution :
You can use VLOOKUP()
In table with the coins symbols and value as you described ( where symbols is column A and name column B)
=VLOOKUP([SYMBOL];[A1:B6];2;FALSE)
Where:
Symbol is the symbol you want to replace ( you can use cell reference );
A1:B6 is the table you want to look in;
2 is the column in wich you take the value;
False as the values are not ordered by a number, just use false.
/!\ depending on your locatioon the ; in my formula might have to be replaced by ,
Here’s an example you could use