0

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

2
  • Welcome to Web Applications Stack Exchange. May 17 '21 at 14:18
  • 1
    I will add one note here. It seems that your "Main" sheet may be a sheet that receives the data in B:Q directly from a form. You really should never add extra columns or formulas (or anything at all) to the raw data form-intake sheet. It nearly always causes problems. Instead, set up a separate sheet that carries over the data from the form-intake sheet (i.e., "mirror" it in a separate sheet) and then add your columns, formulas, etc. Of course, if "Main" is NOT a form-intake sheet, then you can disregard what I've said here.
    – Erik Tyler
    May 17 '21 at 14:40
0

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.

1
  • Thank you so much! Worked like a charm! May 17 '21 at 14:34
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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