How to make variables dynamic in ARRAYFORMULA/QUERY Combination

Question

I'm trying to self populate the first column of the "Main" sheet with a query that returns the first column of the "Finance Office" sheet that matches all three names.

I came up with this

=ARRAYFORMULA(IFS(ROW(A:A)=1,"Finance Office", B:B="","",TRUE,QUERY('Finance Office'!A1:K, "SELECT A WHERE A is not null AND C = '"&C2&"' AND D = '"&D2&"' AND E = '"&E2&"'",0)))

This matches the first row correctly and displays OK as it matches C with C2, D with D2 etc. But the succeeding rows does not auto increment the variables. How can I make the variables increment so that the next row should have C3, D3, and E3 as its variables? The next two rows should not have any matches since they have not been appended in the "Finance Office" sheet.

I'm asking this because I don't want to drag down the formula for every new row that is added from the form. Dragging down works but I was hoping for an automated approach that's why I'm using ARRAYFORMULA.

Included is a link to the spreadsheet. https://docs.google.com/spreadsheets/d/1A8IOW3GntInTUd6uHkjegHPJELKzrhPmW9K4FBK_pks/edit?usp=sharing

Answer 1

Try following formula

=ARRAYFORMULA(
 IFS(
  ROW(A:A)=1,"Finance Office",
  B:B="","",
  TRUE,IFERROR(VLOOKUP(C:C&D:D&E:E,
   {'Finance Office'!C:C&'Finance Office'!D:D&'Finance Office'!E:E,'Finance Office'!A:A},2,0),"")))

It uses VLOOKUP() instead of QUERY() to match values from Finance Office sheet and with IFERROR() you can can leave it blank or add note that no data in Finance Office sheet.

Answer 2

Try this vlookup() formula in cell F1 (in row 1):

=arrayformula( 
  iferror( 
    vlookup( 
      C1:C & D1:D & E1:E, 
      { 
        'Finance Office'!C1:C & 'Finance Office'!D1:D & 'Finance Office'!E1:E, 
        'Finance Office'!F1:Q 
      }, 
      column('Finance Office'!F1:Q) - column('Finance Office'!F1) + 1, 
      false 
    ) 
  ) 
)

This is an array formula that fills the whole result table automatically, so you should clear the range F1:Z to make room for the results. To make the column headers work, ensure that the values in cells C1:E1 are the same in both sheets.