Background:

I’m using a Google Sheets spreadsheet with four tabs. It uses an AppendRow function to add specific data from Sheet2 onto Sheet3, with the data being exported to other spreadsheets from there. Sheet4 contains contact information and other data.

Goal:

I want to use an in-sheet formula, not a script, for this next part. The formula needs to populate Column J of Sheet3. It should take the name field in Column A of Sheet3, match it to Column A of Sheet4, and display the value from Column H of Sheet4 (which is contact information).

Clarifying edit: On Sheet4, the data with contact information starts on Row 6, rather than Row 4 (blank) / Row 5 (first appended result) like on Sheet3, because Sheet4 has longer headers.

That should be straightforward. The challenge I’m running into is that I want to place the formula in cell J3 on Sheet3, but not display any result for J3 or J4. The first match should between A5 and J5, and so forth. I can’t specify a fixed number of rows, because Sheet3 varies in size based on how many records the user is working with.

The formula has to be on Row 3, because those three rows are the only ones that exist on the sheet tab when the user starts entering data. Each additional row is added only when needed, and Row 4 is always blank for some reason.

This is how it should look:

Attempts:

I’ve tried a string of options, using either a VLOOKUP or MATCH/INDEX function, but I keep running into errors. I’m using ARRAYFORMULA as the basis for the function, because I can’t have a formula that is dragged down from row to row, because — again — the rows only show up as users add them.

One attempt was this:

=ARRAYFORMULA(IF(ROW(A3:A) < 5, "", IF(A5:A = "", "", IFERROR(VLOOKUP(A5:A, 'Sheet4'!A$6:H, 8, FALSE), ""))))

That offset the results, so that the first three results still produced addresses, but offset by two rows from the correct individual. The last two rows were simply errors. It looked like this:

Another failed attempt was this:

=ARRAYFORMULA(IF(ROW(A5:A) < 5, "", IFERROR(VLOOKUP(A5:A, 'Sheet3'!$A$6:$H, 8, FALSE), "")))

That attempt at least displayed all five addresses, but offset from the rows with the individuals’ names. It looked like this:

The attempts with INDEX/MATCH ended with similar results, such as the following code:

=ARRAYFORMULA(IF(ROW(A5:A) < 5, "", IF(A5:A = "", "", INDEX('Sheet4'!H$6:H, MATCH(A5:A, 'Sheet4'!A$6:A, 0)))))

That one ended with the same address for all five people, and misaligned by two rows like the second error above:

The other attempts failed in like fashion to the three above.

Question:

How do I make the rows stay aligned, but still not return values for rows 3 and 4, and then match the name with the actual names and addresses from Sheet4?

Thank you!