Google Spreadsheets Query not reacting as expected

0
votes

I'm following the reference for GQL here: https://developers.google.com/chart/interactive/docs/querylanguage

What I was trying to do is select a name from col A, if a value is set in col C, based on the highest value in col H, limited to 1 result.

=QUERY(A5:H100,"select A where (H='"&MAX(H5:H100)&"' and C = 'X') limit 1")

However, all this is doing is returning the first 2 entries from Column A. =MAX(H5:H100) returns 10.

Even Stranger, if I remove the ' surrounding the &MAX(H5:H100)& section, then it still returns the first 2 entries from Column A into the field with the formula, but puts the correct, expected match (the value of A on the row with H=10) into the next row below it.

Am I missing some major piece of the QUERY function?

1
google-sheetsgoogle-query-language
@pnuts I tried in a new blank column. Even cross referenced on another sheet, and with a different function (MIN instead of MAX). Same result. Really clueless why. I even get an error if there's a value in the field below it, stating it will overwrite that value. – Andrew Quackenbos
I changed the max range to be several columns past the data point (A5:M100) and that fixed the issue. Very strange. – Andrew Quackenbos
The "even stranger" is because QUERY guesses (sometimes incorrectly) how many header rows are in the source data if you don't specify in the (optional) third argument. I recommend to always specify the third argument (if there are no header rows, specify 0). – AdamL

1 Answers

0
votes

If ColumnH has numeric values (necessary to find a MAX) you don't want to try to select text (ie leave out a pair of single quotes). Also the middle pair of parentheses is not required and, as suggested by @AdamL, if you don't want a label, blank it out:

=QUERY(A5:H100,"select A where H="&MAX(H5:H100)&" and C = 'X' limit 1 label A ''")