I’m having trouble with a Google Sheets formula that combines web scraping with the QUERY function. Here’s what I’m trying to accomplish:
Pull data from a webpage using IMPORTHTML
Filter that imported data using QUERY to find specific information
Use a cell reference to dynamically set the search criteria
The problem is in my main formula where I’m trying to reference cell C2 as part of the QUERY criteria. When I use the cell reference, I get “query completed with an empty output” error. However, if I replace the cell reference with the actual text value directly in the formula, it works perfectly.
I think my syntax might be wrong when combining the cell reference with the QUERY string. Has anyone encountered this issue before? The formula works fine with hardcoded values but fails when I try to make it dynamic with cell references. Any help would be appreciated!
I’ve run into this tons of times building automated inventory trackers. Usually it’s because IMPORTHTML and QUERY expect different data types. Web data comes in as mixed types even when it looks identical to what you’ve got in your cells. I fix this by forcing consistent data types with TEXT() or VALUE() functions. For text matching, try =QUERY(data,“SELECT * WHERE Col1 = '”&TEXT(C2,“@”)&“'”) - this converts everything to text format. Another thing that trips people up is locale formatting. Dates and numbers from web sources don’t always match your sheet’s regional settings. Test a few manual comparisons first to see if your imported data formats consistently. Sometimes web data has invisible unicode characters that completely break exact matches.
Often, when using cell references in the QUERY function, issues arise from how the query string is structured. To use a cell reference correctly, you need to concatenate it within the query. For instance, if your formula looks like =QUERY(data,"SELECT * WHERE Col1 = 'hardcoded_value'"), modify it to =QUERY(data,"SELECT * WHERE Col1 = '"&C2&"'"). This format, utilizing the ampersands, ensures that both static and dynamic parts are integrated properly. It’s crucial to pay attention to the placement of quote marks around text to avoid syntax errors.
Hit this exact issue last month building a financial tracker. Usually it’s data type mismatches - IMPORTHTML returns one format but your QUERY expects something else. Don’t worry about concatenation syntax first. Check if your imported data has extra spaces or weird formatting. TRIM() around the cell reference fixed most of my cases: =QUERY(data,“SELECT * WHERE Col1 = '”&TRIM(C2)&“'”) Another gotcha - IMPORTHTML often brings numbers in as text. If you’re matching numbers, convert your cell reference or use different operators. Try wrapping it in VALUE() for numeric comparisons. “Empty output” usually means your syntax is fine but no matches found. That screams formatting issue, not concatenation problems.
Same headache here last week with query and importhtml. The timing’s usually the culprit - importhtml needs to load before query kicks in. Wrap it in IFERROR or add a small delay. Also check C2 for trailing spaces - they’ll kill your query instantly. Try using CONTAINS instead of exact match to see if your data’s actually loading.
The concatenation approach works, but there’s another issue to think about. Google Sheets can be weird with data types in QUERY functions, especially with imported web data.
I’ve hit this same problem building automated reports. The issue usually comes down to how IMPORTHTML formats data versus what QUERY wants. Web scraped data often has hidden characters or formatting that breaks queries.
Instead of fighting Google Sheets limitations, I’d set up an automation workflow. You can create a scenario that scrapes web data, processes it properly, and updates your sheet with clean results. This fixes the cell reference problem since you’re working with properly formatted data from the start.
What’s great about this approach is you can add data validation, error handling, and multiple data sources without hitting Google Sheets formula limits. Plus you control when and how data updates.
I’ve used this method for several dashboard projects and it’s way more stable than trying to make complex QUERY formulas work with web scraped data.