CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulagoogle-sheets-query
TC76
TC76

Reputation: 860

QUERY with AND criteria not returning expected results

I have a QUERY that seems to be treating AND more like OR. In other words, when the value of Col11=TRUE and the value of Col12=7, the results are displayed as though Col12=8. Am I missing something? I've tried adding quotes around the variables, parentheses around the two criteria. Adding spacing around the =. What else is there?

Col11 is only TRUE or FALSE values and Col12 is only numeric values from 1-8.

=QUERY({$A$3:$AJ},"SELECT Col3,Col10 where Col11=TRUE and Col12=8",0)

Here's a link to my sheet. It's buried in a larger formula in AK2

AK6 is a good example. It shows U U. It should only show U. It is treat X6 as though it's value is 8 when it is actually 7.

Upvotes: 1

Views: 94

Answers (1)

kirkg13
kirkg13

Reputation: 3010

I believe I worked out what is happening. You are getting two 'U's because I think your inner array is returning multiple rows for Col3='R2-D2', one row where Col23=TRUE and Col24=8, and then another row where Col27=TRUE and Col28=8.

I'm not positive, but I think the values in AK don't relate specifically to the values in that specific row, but instead relate to an array queried across all of your data rows. So as the outer ArrayFormula works down the column, the inner array (with multiple VLOOKUP/ArrayFormula/Queries) is still a large subset of the whole data range. That's assuming I've understood your complex formula correctly - my apologies if I've misunderstood something.

I've added a Heroes-TEST sheet to your sheet. It only has ten rows, all of the R2-D2 data from your Heroes tab. The columns are collapsed for visibility. See what happens when you highlight all the row data below Row3 and press delete - and then UNDO. The two 'U's in column AK become one, because there is only one row of data to query through now. Your original formula is in AK2.

Let me know if this has helped.

Upvotes: 1

Related Questions