Google Sheets: VLOOKUP Using Named Range Fails-CodePudding

I'm using Coingecko to get cryptocurrency prices, and update the data using a 1 hourly Trigger. As the IMPORTDATA function often fails (a known issue using Coingecko), I have a second Trigger than copies this data as a 'backup', using CopyPasteType.PASTE_VALUES.

The primary data is Named Range 'Crypto_Data', the backup is 'Crypto_DataBackup', and the cell's formula is:

=iferror( vlookup(F24,"Crypto_Data",2,false), vlookup(F24,"Crypto_DataBackup",2,false) )

However, the vlookup to "Crypto_DataBackup" fails as it "evaluates to an out of bound range". It works AOK if I just substitute the range of the Named Range (CryptoData!M3:W502).

Does anyone have an idea what the problem could be?

CodePudding user response：

As @Sergey pointed out: "The named range does not need to be enclosed in quotation marks. Change your formula like this: vlookup(F24,Crypto_Data,2,false)"

CodePudding user response：

but if you like quotation marks use:

=IFERROR(VLOOKUP(F24, INDIRECT("Crypto_Data"),       2, ), 
         VLOOKUP(F24, INDIRECT("Crypto_DataBackup"), 2, ))

also, why not:

=IFNA(VLOOKUP(F24, {Crypto_Data; Crypto_DataBackup}, 2, ))