1

Sample Spreadsheet: https://docs.google.com/spreadsheets/d/16p4M5UBODyBhmWFXYrA0FdLsA1FmwSmbrF12VUHlGaw/edit?usp=sharing

I have this formula which works great:

=ARRAYFORMULA(QUERY({Projects!A:C, (REGEXEXTRACT((RIGHT(Projects!C:C,LEN(Projects!C:C)-13)),"[\w]* [\w]*")), Projects!D:H},"SELECT Col1, Col4, Col7 WHERE LOWER(Col3) CONTAINS 'receive' AND LOWER(Col3) CONTAINS 'payment' AND Col9<>'Complete' AND Col9<>'N/A' ORDER BY Col7 ASC LIMIT 8 LABEL Col1 '', Col4 '', Col7 ''"))

I'm trying to show only the results where Projects!A:A (Col1) is within a range on another sheet.

Without the Array, typically I'd write it like this:

=QUERY(FILTER(Projects!A:C,MATCH(Projects!A:A,INDIRECT("'"&$D$2&"'!A:A"), 0)),"SELECT STATEMENT HERE")

When adding the FILTER(MATCH( into the first original working formula you get this:

=ARRAYFORMULA(QUERY({FILTER(Projects!A:C, MATCH(Projects!A:A,INDIRECT("'"&$D$2&"'!A:A"), 0)), (REGEXEXTRACT((RIGHT(Projects!C:C,LEN(Projects!C:C)-13)),"[\w]* [\w]*")), Projects!D:H},"SELECT Col1, Col4, Col7 WHERE LOWER(Col3) CONTAINS 'receive' AND LOWER(Col3) CONTAINS 'payment' AND Col9<>'Complete' AND Col9<>'N/A' ORDER BY Col7 ASC LIMIT 8 LABEL Col1 '', Col4 '', Col7 ''"))

The result is an error:

"Function ARRAY_ROW parameter 2 has mismatched row size. Expected: 2377. Actual: 6816."

The Projects tab is 6816 rows long, but I don't know what is 2377 rows long.

The interesting thing is that when I'm within the Formula bar, the schematic highlighting isn't working for parameter 2 of FILTER. See attached image.enter image description here

How can I add filter the results of Col1 to only show if the results are within another tab's range?

Improve this question

1 Answer 1

Reset to default
1

You can try the following formula instead

=ARRAYFORMULA(QUERY({Projects!A1:C44,REGEXEXTRACT(Projects!C1:C44,"\w+ Payment"), Projects!D1:H44}, 
"SELECT Col1, Col4, Col7 
 WHERE LOWER(Col3) CONTAINS 'receive' AND LOWER(Col3) CONTAINS 'payment' 
      AND Col9<>'Complete' and lower(Col5) contains '"&LOWER(REGEXEXTRACT('Report Helpers'!A1,"(\D+)'"))&"'
 ORDER BY Col7 ASC LIMIT 50 LABEL Col1 'Project', Col4 'Payment', Col7 'Estimated Due Date'"))

The above is based on your existing formula. There are simpler solutions as well if interested.

Improve this answer
6
  • Thanks! I think one issue with the Regex is that the Projects have parentheses, so as currently written isn't pulling them in. I've also tried MATCHES with JOIN but the parentheses messes it up: WHERE Col1 MATCHES '"&JOIN("|",'Report Helpers'!$A1:$A26)&"'
    – Ken
    Dec 13, 2020 at 18:56
  • Thank you!! With a lot of editing for the real data, I got this to work. For anyone else Googling that stumbles upon data that contains parentheses and you're trying to JOIN with regex, here's what I did: - Create a helper tab/column that extracted out the data with the parentheses - Used below formula:
    – Ken
    Dec 13, 2020 at 20:04
  • =ARRAYFORMULA(QUERY({LEFT(Projects!A:A,FIND("(",Projects!A:A)-2),Projects!A:C,REGEXEXTRACT(Projects!C:C,"\w+ Payment"), Projects!D:H}, "SELECT Col2, Col5, Col8 WHERE LOWER(Col4) CONTAINS 'receive' AND LOWER(Col4) CONTAINS 'payment' AND Col1 MATCHES '"&JOIN("|",'Report Helpers'!B3:B)&"' ORDER BY Col7 ASC LABEL Col2 '', Col5 '', Col8 ''"))
    – Ken
    Dec 13, 2020 at 20:06
  • 1
    "one issue with the Regex is that the Projects have parentheses...". The parentheses played no part on the formulas you mentioned in your question. You never mentioned where Col1 MATCHES. In any case you can also use the following as your regex. For content within the parentheses =REGEXEXTRACT(A2,"\((.*)\)"). For content before the parentheses =REGEXEXTRACT(A2,"(.*) \(")
    – marikamitsos
    Dec 13, 2020 at 22:09
  • Good advice, I did remove it from the bin. I upvoted your answer but as written (and I just tested again in the Sample sheet), it doesn't work. Hence my edit in an above comment with the working formula. Matches Join was mentioned in a comment above as an additional attempt. Anyways thank you!
    – Ken
    Dec 13, 2020 at 23:43

Your Answer

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

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