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.
Data!
should be imported, hence the filter.