CODEWITHSUNDEEP
google-sheets
Nick
Nick

Reputation: 4766

How do I get QUERY function to return correct data?

So I have this spreadsheet with data in it, there are 29 columns and 54 rows.

On the 2nd sheet I'm trying to find all of the rows that fit a certain criteria.

For some reason, if I include the column X in my query data, the results are completely messed up. The 1st row of the result is just concatenating the first 23 rows together whether they fit the criteria or not. If I only include up to Column W the query is OK and it returns the correct results. But the problem is that I need to get data from Columns A and AB, so I need to include column X in my data range.

In this spreadsheet you can see the data on Sheet1, the query that includes column X on Sheet2, and on Sheet3 I have the same exact query except it only goes up to Column W and you can see the correct results there.

Basically, I need the query to return the value of Column A and Column AB for every row where Column B is marked with an "x".

Here is the sheet

Upvotes: 0

Views: 227

Answers (2)

Aurielle Perlmann
Aurielle Perlmann

Reputation: 5509

This is a duplicate of https://webapps.stackexchange.com/questions/103761/how-do-i-get-query-to-return-the-right-data which I answered hours ago:

You can use the Filter function to do this , with a literal array :

enter image description here

Upvotes: 1

user6655984
user6655984

Reputation:

Include the third parameter of query, which is the number of header rows:

=query(Sheet1!A2:X, "select A where B='x'", 1)

The parameter is optional, but if it's omitted, query will guess the number of header rows based on the data. Sometimes it guesses correctly, sometimes not (hence the dependence on what columns are included in the query). In your case, it decided that the table had 23 header rows and concatenated them in the output.

I don't know why you have arrayformula wrapper for query, it does not really do anything.

Upvotes: 1

Related Questions