0

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.

  • 1
    This looks like a XY Problem. Please add a broader description of the result that you want including sample data input and the corresponding expected result. – Rubén Nov 12 '16 at 20:40
  • Sorry about that! Hope I was able to clarify – Comfort Eagle Nov 12 '16 at 21:16
  • This is merely a sample sheet. Not everything in Data! should be imported, hence the filter. – Comfort Eagle Nov 12 '16 at 21:28
  • 1
    The demo spreadsheet doesn't include a sheet with the expect result. The original Sheet1 didn't help to clarify what are you expecting as the result, by one side because the headers are the same (same name, same order) than those of the original Data sheet. – Rubén Nov 12 '16 at 21:30
  • Added source data, formula input and expected result.. The data was formatted using this tool: senseful.github.io/web-tools/text-table – Rubén Nov 12 '16 at 21:47
3

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.

  • This works perfect, thank you so much!!! – Comfort Eagle Nov 12 '16 at 21:44

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.