ARRAYFORMULA() won't iterate over range

Question

I'm trying to get all rows of data into a sheet where column headers match. Please have a look at this sample sheet. I need to achieve this dynamically so I'm using ARRAYFORMULA().

Source data

+----+-------+-------+-------+-------+-------+-------+-------+-------+-------+
|    |   A   |   B   |   C   |   D   |   E   |   F   |   G   |   H   |   I   |
+----+-------+-------+-------+-------+-------+-------+-------+-------+-------+
|  1 | ID101 | ID999 | ID102 | ID103 | ID104 | ID105 | ID106 | ID107 | ID108 |
|  2 | 2     | 9     | 3     | 1     | 5     | 1     | 3     | 1     | 5     |
|  3 | 1     | 9     | 3     | 3     | 1     | 3     | 2     | 4     | 4     |
|  4 | 1     | 9     | 4     | 2     | 4     | 2     | 4     | 5     | 5     |
|  5 | 5     | 9     | 2     | 5     | 2     | 3     | 1     | 4     | 3     |
|  6 | 3     | 9     | 2     | 4     | 2     | 3     | 2     | 2     | 4     |
|  7 | 2     | 9     | 2     | 2     | 2     | 3     | 3     | 2     | 4     |
|  8 | 2     | 9     | 2     | 4     | 1     | 4     | 1     | 4     | 3     |
|  9 | 2     | 9     | 3     | 2     | 3     | 1     | 4     | 1     | 5     |
| 10 | 2     | 9     | 3     | 2     | 3     | 1     | 4     | 1     | 5     |
+----+-------+-------+-------+-------+-------+-------+-------+-------+-------+

Formula input

+---+-------+-------+-------+-------+-------+-------+-------+-------+
|   |   A   |   B   |   C   |   D   |   E   |   F   |   G   |   H   |
+---+-------+-------+-------+-------+-------+-------+-------+-------+
| 1 | ID101 | ID102 | ID103 | ID104 | ID105 | ID106 | ID107 | ID108 |
+---+-------+-------+-------+-------+-------+-------+-------+-------+

Expected result

+----+-------+-------+-------+-------+-------+-------+-------+-------+
|    |   A   |   B   |   C   |   D   |   E   |   F   |   G   |   H   |
+----+-------+-------+-------+-------+-------+-------+-------+-------+
|  1 | ID101 | ID102 | ID103 | ID104 | ID105 | ID106 | ID107 | ID108 |
|  2 | 2     | 3     | 1     | 5     | 1     | 3     | 1     | 5     |
|  3 | 1     | 3     | 3     | 1     | 3     | 2     | 4     | 4     |
|  4 | 1     | 4     | 2     | 4     | 2     | 4     | 5     | 5     |
|  5 | 5     | 2     | 5     | 2     | 3     | 1     | 4     | 3     |
|  6 | 3     | 2     | 4     | 2     | 3     | 2     | 2     | 4     |
|  7 | 2     | 2     | 2     | 2     | 3     | 3     | 2     | 4     |
|  8 | 2     | 2     | 4     | 1     | 4     | 1     | 4     | 3     |
|  9 | 2     | 3     | 2     | 3     | 1     | 4     | 1     | 5     |
| 10 | 2     | 3     | 2     | 3     | 1     | 4     | 1     | 5     |
+----+-------+-------+-------+-------+-------+-------+-------+-------+

---

Please consider the following formula in Sheet1!A2:

=ARRAYFORMULA(IF(LEN(A1:1),FILTER(Data!$A$2:$AAA,Data!$A1:1=G1),))

The problem with this formula is G1 in the second FILTER() parameter, which appears to be an absolute reference.

I've tried G1:1 but that doesn't seem to trigger an iteration by ARRAYFORMULA(), which I find hard to understand, since ARRAYFORMULA(G1:1) would trigger an expansion by itself.

Been on this for hours without any luck, so really grateful for help.

Answer 1

Formula

=ArrayFormula(HLOOKUP(A1:H1,Data!A:I,ROW(Data!A2:A),FALSE))

Explanation

Not all the Google Sheets formulas "iterate" when they are nested inside ArrayFormula by the other hand, FILTER is similar to ArrayFormula as it returns an array of values.

Instead use nest ROW inside HLOOKUP, as it's shown on the formula section of this answer.