I’m having trouble using the QUERY function on cells that contain formulas instead of plain values. Whenever I try to query a range where the data comes from formulas, I keep getting parse errors and the query won’t work properly.
For example, let’s say I have a column with =SUM(A1:A5) or =VLOOKUP(B2, DataRange, 2, FALSE) formulas, and I want to run something like =QUERY(C1:C10, "SELECT * WHERE C > 100") on that data.
Does anyone know if there’s a workaround for this issue? Do I really have to copy and paste values manually every time, or is there a better way to make queries work with formula results?
QUERY works with formula results, but a few things can mess it up. Make sure your formulas finish calculating before the query runs - there’s often a timing issue where QUERY fires before the formulas update. Also check that your column data types match throughout the range. If some formulas return numbers and others return text or blanks, QUERY gets confused. I wrap problematic formulas with IFERROR or use arrayformulas to stabilize the data. Try referencing a bigger range than you need and let QUERY do the filtering instead of using complex nested formulas.
This issue arises because Google Sheets may not have completed calculating your formulas when the QUERY function attempts to read the data. I’ve found that using INDIRECT can help by ensuring proper evaluation—try =QUERY(INDIRECT("C1:C10"), "SELECT * WHERE Col1 > 100") instead of directly referencing the range. Another effective method is to create a helper column with =ARRAYFORMULA(VALUE(C1:C10)) to convert your formula results into actual values, then query that helper range. This approach addresses timing issues between calculations and queries and can resolve parsing problems due to weird formatting.
totally agree! i had similar issues with QUERY not recognizing results when formulas return errors or text. make sure your data is clean, no #N/A or other errors. also, confirm that you’re working with actual numbers for comparisons to avoid those parse errors.