Query function unable to use array data range reference

Question

Goal:

I want to optimize the data processing speed of the Query function, which is actually properly functioning, however there are times that it's extremely slow (1-2 minutes). So I'm in the process of improving my entire sheet. I referred to this: https://webapps.stackexchange.com/questions/106809/how-can-i-speed-up-google-sheets

With that said, I think I can improve the below Query setup by changing the data range from a regular reference to an array range reference.

How the current query works:

The below screenshot is a Query function that pulls data from a sheet (Blend Data, which also pulls data from another sheet) and counts the number of statuses (Technical Issue, In Consult, In Progress and Need Info) for each person (agents).

Here's what I have for the Query function.

=QUERY(
    'Blend Data'!$A$3:$C,
    "select C, COUNT(C) where B matches 
    'In Progress|In Consult|Need Info|Technical Issue'
    AND C matches'"
    &IFERROR(VLOOKUP(Values!F2,Values!F2:F,1,FALSE))&"|"
    &IFERROR(VLOOKUP(Values!F3,Values!F2:F,1,FALSE))&"|"
    &IFERROR(VLOOKUP(Values!F4,Values!F2:F,1,FALSE))&"|"
    &IFERROR(VLOOKUP(Values!F5,Values!F2:F,1,FALSE))&"|"
    &IFERROR(VLOOKUP(Values!F6,Values!F2:F,1,FALSE))&"'
    GROUP BY C pivot B",
    1)

Details about the Query setup:

• Blend Data: Is a sheet in the same spreadsheet that pulls data from 2 sheets within the same spreadsheet, which includes information about the agents and tickets.
• VLOOKUP: The "Values!F2" is pulling agent names from a separate sheet in the same spreadsheet. I'd like to use the reference instead of hard coding it as the names can sometimes change.

More about the "Blend Data sheet":

The Blend Data sheet is using the Query function to pull 2 separate sheets ("Sheet A Data" sheet pulling data from a different Spreadsheet, "Sheet B Data" sheet pulling data from a different Spreadsheet) and I was able to do this with the Importrange function. Here's a screenshot.

Here's what I have for the Query function.

=QUERY(
    {
        IMPORTRANGE("1DeXTU7dJVJFYIHSP8U9dsKw3YNZVtEZIjbURAd54Xjs","'Sheet A Data'!$A$3:$H");
        IMPORTRANGE("1QUZ-ljkXNOvtnPg-a2FqM2PhYUdt_zX5gMLtB6GcFdc","'Sheet B Data'!$A$3:$H")
    },
    A1,
    1
)

Question:

As mentioned above, I want to change the data to an array format; however, I got an error (Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: C).

Here's what I tried to do.

=QUERY(
    {
        IMPORTRANGE("1DeXTU7dJVJFYIHSP8U9dsKw3YNZVtEZIjbURAd54Xjs","'Sheet A Data'!$A$3:$H");
        IMPORTRANGE("1QUZ-ljkXNOvtnPg-a2FqM2PhYUdt_zX5gMLtB6GcFdc","'Sheet B Data'!$A$3:$H")
    },
    "select C, COUNT(C) where B matches 'In Progress|In Consult|Need Info|Blocked By' AND C matches '"
    &IFERROR(VLOOKUP(Values!F2,Values!F2:F,1,FALSE))&"|"
    &IFERROR(VLOOKUP(Values!F3,Values!F2:F,1,FALSE))&"|"
    &IFERROR(VLOOKUP(Values!F4,Values!F2:F,1,FALSE))&"|"
    &IFERROR(VLOOKUP(Values!F5,Values!F2:F,1,FALSE))&"|"
    &IFERROR(VLOOKUP(Values!F6,Values!F2:F,1,FALSE))&"'
    GROUP BY C pivot B",
  1
)

Answer 1

when you use constructed range {} you cant use A,B,C references in query. instead, you need to use Col1,Col2,Col3 notation

=QUERY({
 IMPORTRANGE("1DeXTU7dJVJFYIHSP8U9dsKw3YNZVtEZIjbURAd54Xjs", "Sheet A Data!A3:H");
 IMPORTRANGE("1QUZ-ljkXNOvtnPg-a2FqM2PhYUdt_zX5gMLtB6GcFdc", "Sheet B Data!A3:H")},
 "select Col3,count(Col3) 
  where Col2 matches 'In Progress|In Consult|Need Info|Blocked By' 
    and Col3 matches '"
  &IFERROR(VLOOKUP(Values!F2, Values!F2:F, 1, 0))&"|"
  &IFERROR(VLOOKUP(Values!F3, Values!F2:F, 1, 0))&"|"
  &IFERROR(VLOOKUP(Values!F4, Values!F2:F, 1, 0))&"|"
  &IFERROR(VLOOKUP(Values!F5, Values!F2:F, 1, 0))&"|"
  &IFERROR(VLOOKUP(Values!F6, Values!F2:F, 1, 0))&"'
  group by Col3 
  pivot Col2", 1)