1

I want to get a subset of data from another tab. Eventually, this will become a script and pull data from a different sheet.

I can use QUERY to get all the data available, but I only want to return rows where column C contains a specific value. This lookup value is currently held in a range, so it seems I should be able to use MATCH inside the QUERY.

test query

In the above test, I have checked that MATCH works on the lookup range:

=MATCH("Sandra",VisitPeople, 0)

(returns 2)

and that QUERY with a similar Where clause returns a superset of data:

=QUERY('Contact details'!A1:AX, "select C,D,E where B = 'Volunteer' and E <> ''", 1)

(returns list of names)

but when I try to put the two together, I get an error:

=QUERY('Contact details'!A2:AX, "select C,D,E where B = 'Volunteer' and MATCH(C,VisitPeople,0) > 0", 1)

(returns Formula parse error.)

How should I pass column C into the MATCH function?

Improve this question

1 Answer 1

Reset to default
0

You can't combine formulas with QUERY in such way you try - the syntax is all wrong. QUERY can't handle arrays/ranges inside itself so you need to do something like this:

={'CONTACT DETAILS'!C1:E1; 
 FILTER(
  QUERY('CONTACT DETAILS'!A1:AX, 
  "select C,D,E where B = 'Volunteer' and E <> ''", 0),
 COUNTIF(
  QUERY('CONTACT DETAILS'!A1:AX, 
  "select C where B = 'Volunteer' and E <> ''", 0),
 INDIRECT("A4:A"&COUNTA(
  QUERY('CONTACT DETAILS'!A1:AX, 
  "select C where B = 'Volunteer' and E <> ''", 0))+3)))}

0

note: +3 is correction for list of names starting at A4:A

Improve this answer
2
  • Yikes! Every time I try to do something that should be simple, it turns out crazy complicated. I don't understand all that you have given here... The outside {} glues it all together, and the first part followed by ; inserts the headings and directs that the next part will be underneath (a , would put it alongside). FILTER(QUERY means that all of the QUERY data will be pulled back before being filtered. What I don't understand is why COUNTIF is needed and what INDIRECT with COUNTA is doing. [It would be sooo simple in SQL]
    – Sarah
    Commented May 4, 2019 at 10:52
  • countif is part of 2nd filter parameter and basicaly its there to C:E based on list of items in A column. whole array (lets say A1:A1000) would took too long to calculate and also it would mess up stuff coz of blank cells so we use INDIRECT just to use COUNTA and count how many names are in list in A column and it will output range A4:A6 which is much faster to calculate than A4:A1000
    – user0
    Commented May 4, 2019 at 20:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.