[Solved] Perform table lookup using alias for key

[youni]

Hello! I have a table of my wallets where I typed coins and amount of coins. And I also count usd value with this formula:

Code: Select all

=IFERROR(ROUND(C2*INDEX($Prices.$A$2:$Prices.$B$180,MATCH(B2,$Prices.$A$2:$Prices.$A$180,0),2),2),"")

Sheet "Wallets":

Code: Select all

Wallet	Coin	Amount	USD
Xeggex	BTC	0.0002	11.56
Xeggex	ETH	0.01	24.2
Xeggex	HYPRA	45000	
Xeggex	SOL	20	2740
TradeOgre	HYPRA	12000	

I use page Prices where I placed prices of coins like this (and I update it from time to time)

Sheet "Prices"

Code: Select all

Coin	Price
BTC	57800
ETH	2420
HYP	0.0017
SOL	137

As you can see, coin HYP (the name of this coin on coingecko) has alias HYPRA in Xeggex. And I wish to use this name HYPRA as Xeggex uses. But I wish to use name HYP on sheet "Prices" as everybody others use. So i defined sheet "Aliases"

Sheet "Aliases":

Code: Select all

Coin	Where	Alias
HYP	Xeggex	HYPRA
HYP	Tradeogre	HYPRA
SMARTIE	Xeggex	SMT

How to improve my formula for count amount in usd using aliases on certain exchange?

Code: Select all

=IFERROR(ROUND(C2*INDEX($Prices.$A$2:$Prices.$B$180,MATCH(B2,$Prices.$A$2:$Prices.$A$180,0),2),2),"")

I attach my file aliases.ods.

[MrProgrammer]

How to improve my formula for count amount in usd using aliases on certain exchange?

Wallets column D: Try to match Wallet and Alias columns in Aliases sheet.
Wallets column E: If matched, that gives Coin, otherwise use Alias for Coin column.
Wallets column G: Coin value in USD from Prices sheet times Amount column gives Price.

202409150921.ods

(13.12 KiB) Downloaded 55 times

If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know