1

tl;dr

How to create an 1D range from a 2D range output of an ARRAYFORMULA.

Intro

I have a Google Sheet that uses a very complex ARRAYFORMULA to perform data validation on about fift cells in a row. Each cell returns a result, most will be blank but one of the cells may return a string or a number. I need that returned value, AND I need the ARRAYFORMULA to also operate on a huge number of rows.

This seems like a question asked by many people on the web, but with no answer anywhere (that I can find).

In the below I have replaces the complex formula with a simpler one and a simple set of test data, and will go through the process I've attempted step by step.


ARRAYFORMULA, one row, output to multiple cells

In E2, the following formula: =ARRAYFORMULA(if(A2=B2:D2,B2:D2,""))
In E3, the following formula: =ARRAYFORMULA(if(A3=B3:D3,B3:D3,""))
In E4, the following formula: =ARRAYFORMULA(if(A4=B4:D4,B4:D4,""))

┌───────┬───────┬───────┬───────┬────────┬────────────┬───────┬───────┐
│       │   A   │   B   │   C   │    D   │      E     │   F   │   G   │
├───────┼───────┼───────┼───────┼────────┼────────────┼───────┼───────┤
│   1   │ Input │ Val1  │  Val2 │  Val3  │Formula/out1│ out2  │ out3  │
├───────┼───────┼───────┼───────┼────────┼────────────┼───────┼───────┤
│   2   │Test00 │Test00 │Test01 │Test02  │   Test00   │       │       │
├───────┼───────┼───────┼───────┼────────┼────────────┼───────┼───────┤
│   3   │Foo    │Test10 │Test11 │Test12  │            │       │       │
├───────┼───────┼───────┼───────┼────────┼────────────┼───────┼───────┤
│   4   │Test22 │Test20 │Test21 │Test22  │            │       │Test22 │
└───────┴───────┴───────┴───────┴────────┴────────────┴───────┴───────┘


As can be seen from the above, the input 'Test00' were found at 0,0 in the input range, and therefore placed at E2, 0,0 in the output range. Similar 'Test22' were found at 0,2 in the input range, and therefore placed at G4, 0,2 in the output range. 'Foo' were not found, so output for that row remains blank.


ARRAYFORMULA, one row, output to single cell

Given that I know there will maximum be one value per row, and that all other values will be blanks, I can use CONCATENATE to ensure the value is returned on the E column:


In E2, the following formula: =ARRAYFORMULA(CONCATENATE(if(A2=B2:D2,B2:D2,"")))
In E3, the following formula: =ARRAYFORMULA(CONCATENATE(if(A3=B3:D3,B3:D3,"")))
In E4, the following formula: =ARRAYFORMULA(CONCATENATE(if(A4=B4:D4,B4:D4,"")))

┌───────┬───────┬───────┬───────┬────────┬────────────┬───────┬───────┐
│       │   A   │   B   │   C   │    D   │      E     │   F   │   G   │
├───────┼───────┼───────┼───────┼────────┼────────────┼───────┼───────┤
│   1   │ Input │ Val1  │  Val2 │  Val3  │Formula/out1│       │       │
├───────┼───────┼───────┼───────┼────────┼────────────┼───────┼───────┤
│   2   │Test00 │Test00 │Test01 │Test02  │   Test00   │       │       │
├───────┼───────┼───────┼───────┼────────┼────────────┼───────┼───────┤
│   3   │Foo    │Test10 │Test11 │Test12  │            │       │       │
├───────┼───────┼───────┼───────┼────────┼────────────┼───────┼───────┤
│   4   │Test22 │Test20 │Test21 │Test22  │   Test22   │       │       │
└───────┴───────┴───────┴───────┴────────┴────────────┴───────┴───────┘


As can be seen from the above, the output is now only in the E column - just as I wanted. However, I still have to copy the formula to every row I need this performed on, and that's a lot of rows!


ARRAYFORMULA, multiple rows, output to 2D range

In E2, the following formula: =ARRAYFORMULA(if(A2:A4=B2:D4,B2:D4,""))

┌───────┬───────┬───────┬───────┬────────┬────────────┬───────┬───────┐
│       │   A   │   B   │   C   │    D   │      E     │   F   │   G   │
├───────┼───────┼───────┼───────┼────────┼────────────┼───────┼───────┤
│   1   │ Input │ Val1  │  Val2 │  Val3  │Formula/out1│ out2  │ out3  │
├───────┼───────┼───────┼───────┼────────┼────────────┼───────┼───────┤
│   2   │Test00 │Test00 │Test01 │Test02  │   Test00   │       │       │
├───────┼───────┼───────┼───────┼────────┼────────────┼───────┼───────┤
│   3   │Foo    │Test10 │Test11 │Test12  │            │       │       │
├───────┼───────┼───────┼───────┼────────┼────────────┼───────┼───────┤
│   4   │Test22 │Test20 │Test21 │Test22  │            │       │Test22 │
└───────┴───────┴───────┴───────┴────────┴────────────┴───────┴───────┘


As can be seen from the above, the single formula perfectly performs the role of the three formulas given in the first test, but I'm back to the problem that the output is a 2D range, instead of just a 1D range, with output only to the E column.


ARRAYFORMULA, multiple rows, output to 1D range

This is where I run into problems. The CONCATENATE trick used above does not work on 2D ranges, as all the results is output into only one cell (E2), such as Test00Test22. :-(

What I'm looking for is something that for each row in a range returns the first non-blank value.

A variation of the typical approach to search for the first non-blank in a range is this formula: =Arrayformula(ifna(INDEX(E2:H2,MATCH(FALSE,E2:H2="",0)),"")). If inserted into G2 (and then copied from G2 to G3 and G4), the formula will correctly extract the first non-blank value of the range.

But how can I combine these two formulas?
Also, given that a range is provided twice in the formula to find the first non-blank cell, it occur to be that I will have to include the original ArrayFormula twice in the combined formula, which is something I would like to avoid.

What other alternatives do I have to convert the 2D range from the Arrayformula into an 1D range?

0

Ended up answering my own question...

After a lot of research, trail and especially errors, I were finally able to come up with a generic formula that would allow me to insert any ARRAYFORMULA construct (in fact any function that output a range) into another ARRAYFORMULA and provide a separate output for each row.

={"Header";TRANSPOSE(ARRAYFORMULA(TRIM(QUERY(TRANSPOSE({ INSERT_ARRAYFORMULA }),,2^99))))}

Instead of CONCATENATE, it uses QUERY to perform the concatenation.

To use it in my example, insert the following formula in E1:
={"Formula/out";TRANSPOSE(ARRAYFORMULA(TRIM(QUERY(TRANSPOSE({ ARRAYFORMULA(if(A2:A4=B2:D4,B2:D4,"")) }),,2^99))))}

The result:

┌───────┬───────┬───────┬───────┬────────┬────────────┬───────┬───────┐
│       │   A   │   B   │   C   │    D   │      E     │   F   │   G   │
├───────┼───────┼───────┼───────┼────────┼────────────┼───────┼───────┤
│   1   │ Input │ Val1  │  Val2 │  Val3  │Formula/out │       │       │
├───────┼───────┼───────┼───────┼────────┼────────────┼───────┼───────┤
│   2   │Test00 │Test00 │Test01 │Test02  │   Test00   │       │       │ Test string.
├───────┼───────┼───────┼───────┼────────┼────────────┼───────┼───────┤
│   3   │Foo    │Test10 │Test11 │Test12  │            │       │       │ Test wrong input
├───────┼───────┼───────┼───────┼────────┼────────────┼───────┼───────┤
│   4   │Test22 │Test20 │Test21 │Test22  │   Test22   │       │       │ Test string
├───────┼───────┼───────┼───────┼────────┼────────────┼───────┼───────┤
│   5   │     6 │      2│     6 │     10 │      6     │       │       │ Test numbers
├───────┼───────┼───────┼───────┼────────┼────────────┼───────┼───────┤
│   6   │       │      2│     6 │     10 │            │       │       │ Test no Input
└───────┴───────┴───────┴───────┴────────┴────────────┴───────┴───────┘

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.