How can I combine a Google Sheets query formula with arrayFormula formulas?

Question

Code from @doubleunary's answer to: How can I improve the performance of this Google Sheets custom function?

My 'query' sheet has a query formula to filter an 'INPUT' sheet. The query is:

=query(INPUT!A1:K, "select A, max(G), H where I = 'Pilot' group by A, H pivot D", 1)

The 'INPUT' sheet values are:

Instance Id	Group name	Group Id	Field name	Field Id	Type	Value	File Id	Role	Concatenated Id	VLookup value
Instance1A	Widgets	91c7db0a-c52a-407d-869a-af8ba8bf8ba7	Field 1	09456c1a-abb4-4e81-94bd-7ce4c88afffc	CURRENCY	100	Pilot_File	Pilot	09456c1a-abb4-4e81-94bd-7ce4c88afffcInstance1A	100
Instance1A	Widgets	91c7db0a-c52a-407d-869a-af8ba8bf8ba7	Field 2	474f6395-83a7-4c2b-aa5a-ceb00e200f8e	CURRENCY	200	Pilot_File	Pilot	474f6395-83a7-4c2b-aa5a-ceb00e200f8eInstance1A	200
Instance1A	Widgets	91c7db0a-c52a-407d-869a-af8ba8bf8ba7	Field 3	ac64e001-fe85-400a-92e4-69cebf1c260d	CURRENCY	300	Pilot_File	Pilot	ac64e001-fe85-400a-92e4-69cebf1c260dInstance1A	300
Instance1B	Widgets	91c7db0a-c52a-407d-869a-af8ba8bf8ba7	Field 1	09456c1a-abb4-4e81-94bd-7ce4c88afffc	CURRENCY	110	Pilot_File	Pilot	09456c1a-abb4-4e81-94bd-7ce4c88afffcInstance1B	110
Instance1B	Widgets	91c7db0a-c52a-407d-869a-af8ba8bf8ba7	Field 2	474f6395-83a7-4c2b-aa5a-ceb00e200f8e	CURRENCY	220	Pilot_File	Pilot	474f6395-83a7-4c2b-aa5a-ceb00e200f8eInstance1B	220
Instance1B	Widgets	91c7db0a-c52a-407d-869a-af8ba8bf8ba7	Field 3	ac64e001-fe85-400a-92e4-69cebf1c260d	CURRENCY	330	Pilot_File	Pilot	ac64e001-fe85-400a-92e4-69cebf1c260dInstance1B	330
Instance2A	Widgets	91c7db0a-c52a-407d-869a-af8ba8bf8ba7	Field 1	09456c1a-abb4-4e81-94bd-7ce4c88afffc	CURRENCY	1000	Co-PIlot_File	Co-Pilot	09456c1a-abb4-4e81-94bd-7ce4c88afffcInstance2A	1000
Instance2A	Widgets	91c7db0a-c52a-407d-869a-af8ba8bf8ba7	Field 2	474f6395-83a7-4c2b-aa5a-ceb00e200f8e	CURRENCY	2000	Co-PIlot_File	Co-Pilot	474f6395-83a7-4c2b-aa5a-ceb00e200f8eInstance2A	2000
Instance2A	Widgets	91c7db0a-c52a-407d-869a-af8ba8bf8ba7	Field 3	ac64e001-fe85-400a-92e4-69cebf1c260d	CURRENCY	3000	Co-PIlot_File	Co-Pilot	ac64e001-fe85-400a-92e4-69cebf1c260dInstance2A	3000

Which gives the following result on the 'query' sheet:

Instance Id	Field 1	Field 2	Field 3	File Id
Instance1A	100	200	300	Pilot_File
Instance1B	110	220	330	Pilot_File

Another 'RESULTS_PILOT' sheet uses two arrayFormula formulas to format the 'query' sheet values into a table with values placed in specific columns according to a 'TABLE_CONFIG' sheet. The formulas are:

cell A1:

=arrayformula( 
  { 
    "Instance Id", 
    iferror( 
      vlookup( 
        sequence(1, max(TABLE_CONFIG!C2:C4) - 1, 2), 
        { TABLE_CONFIG!C2:C, TABLE_CONFIG!B2:B }, 
        2, false 
      ) 
    ), 
    "File Id" 
  } 
)

cell A2:

=arrayformula( 
  iferror( 
    hlookup( 
      A1:I1, 
      query!A1:E, 
      sequence(counta(query!A2:A), 1, 2), 
      false 
    ) 
  ) 
)

The 'TABLE_CONFIG' sheet is:

Field Id	Description	Desired table field column	Group Id
09456c1a-abb4-4e81-94bd-7ce4c88afffc	Field 1	1	91c7db0a-c52a-407d-869a-af8ba8bf8ba7
474f6395-83a7-4c2b-aa5a-ceb00e200f8e	Field 2	3	91c7db0a-c52a-407d-869a-af8ba8bf8ba7
ac64e001-fe85-400a-92e4-69cebf1c260d	Field 3	5	91c7db0a-c52a-407d-869a-af8ba8bf8ba7

So the table on the 'RESULTS_PILOT' sheet appears like:

Instance Id	Field 1		Field 2		Field 3	File Id
Instance1A	100		200		300	Pilot_File
Instance1B	110		220		330	Pilot_File

Is there a way to combine the query with the arrayFormula formulas on the 'RESULTS_PILOT' sheet, so that the 'query' sheet is not required?

Answer 1

try:

=INDEX(LAMBDA(x, y, {y; IFERROR(HLOOKUP(y, x, 
 SEQUENCE(COUNTA(QUERY(x, "offset 1", )), 1, 2), ))}) 
 (QUERY(INPUT!A1:K, "select A, max(G), H where I = 'Pilot' group by A, H pivot D", 1), 
 {"Instance Id", IFERROR(VLOOKUP(SEQUENCE(1, MAX(TABLE_CONFIG!C2:C4)-1, 2), 
 {TABLE_CONFIG!C2:C, TABLE_CONFIG!B2:B }, 2, )), "File Id"}))