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.

5
  • 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, 2016 at 20:40
  • Sorry about that! Hope I was able to clarify Nov 12, 2016 at 21:16
  • This is merely a sample sheet. Not everything in Data! should be imported, hence the filter. Nov 12, 2016 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, 2016 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, 2016 at 21:47

1 Answer 1

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.

1
  • This works perfect, thank you so much!!! Nov 12, 2016 at 21:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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