#VALUE error when multiplying VLOOKUP from web queried table

Question

I am getting #VALUE for my Cur Portfolio Value which is just multiplying the results of SUMIF (Shares) and VLOOKUP to a web queried table (Cur Market Value), literally JUST D13*E13. All cells and ranges have been formatted to accounting, number, general with the same error result. I know the web queried table is the issue b/c when I tried the Shares by the number typed in for Cur Market Value, it worked, but I need it to be able to be refreshed.

Company	Symbol	Shares	Cur Market Value	Cur Portfolio Value	Total Profit/Loss
DOW	DJI	23.00	‎41,393.78	#VALUE!	#VALUE!
Bitcoin	BTC	-	‎59,977.50	#VALUE!	#VALUE!
AMAZON.COM, INC.	AMZN	363.00	‎186.49	#VALUE!	#VALUE!
Ethereum	ETH	-	‎2,427.27	#VALUE!	#VALUE!
BNB	BNB	-	‎554.95	#VALUE!	#VALUE!
S&P 500	INX	38.00	‎5,626.02	#VALUE!	#VALUE!
AMC ENTERTAINMENT HOLDINGS, INC.	AMC	92.00	‎4.96	#VALUE!	#VALUE!
Cardano	ADA	33.00	‎0.36	#VALUE!	#VALUE!
GAMESTOP CORP.	GME	42.00	‎20.65	#VALUE!	#VALUE!
MICROSOFT CORPORATION	MSFT	16.00	‎430.59	#VALUE!	#VALUE!

Answer 1

Hi, Christina C_HaloKitty217

Thank you for using Microsoft products and posting them to the community.

Yes, it's basically impossible for this to be a function combination issue.

Is your web lookup table imported externally through Excel's data? Or is the StockHistory function being used?

This is most likely a problem with the network connection, which is causing it to not get the data or the data is not refreshed in a timely manner.

Currently there are several types of data that you can use to get updates through Excel:

Use the data query function:

In Excel, use the “Get Data” function under the “Data” tab to import data from the Web or other data sources.

Set the data refresh frequency so that it is updated regularly.

Use Power Query:

Use Power Query in Excel to connect to a data source.

Set the Auto Refresh option to refresh the data when the workbook is opened or periodically.

API interface:

If there is an API that provides real-time data, you can use Excel's Power Query or VBA to write scripts to call the API and get the data.

Third-party add-ins:

Use third-party add-ins or tools, which often provide real-time market data, and integrate them into Excel.

I hope the above information can help you. Feel free to send a message if you need further help.

Best wishes

Aiden.C - MSFT |Microsoft Community Support Specialist

Answer 2

Your values are coming through as text and appear to have a few additional characters in them. Based on a copy/paste of y our data, this may work for you:

=D13*TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(E13,CHAR(160),""),UNICHAR(8206),"")))

though it would be better to try and fix the data at the source - i.e. in y our web query.