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