This post describes how to select a required column from an array formula result in Google Docs Sheets.

If one of your formulas returns multiple columns, you may want to extract any specific column(s) from the beginning, middle, or at any position.

Do you know how to select only the required column or columns from an array result in Google Sheets?

We can use three Google Sheets functions to select only the required columns from a multiple column output. They are Array_Constrain, Query, and Index.

Must Check: Google Sheets Function Guide.

Some of you may ask why can’t the Offset function.

We can’t use the Offset function for the said purpose as it may require the first cell reference of a physical array/range, not an expression.

That means use Offset with physical columns not virtual columns like the result of a formula (expression).

You may not be able to replace an Array_Constrain formula with an Index formula or vice versa. As it may suites for different scenarios.

But in the case of selecting only the required column or columns in Google Sheets, we can replace the above two functions with Query.

Sample Data:

Here is the sample data (A1:D9) for our test. The Filter formula in cell F1 filters the rows that contain the name “Evan” in column A.

We want to use the Filter formula as the array result and select only the required column(s) from it.

Let’s see how to use the Array_Constrain, Index, and Query formulas for this and their pros and cons.

Constrain Columns in Google Sheets Using the Index Function

Pros:

• Shortest formula to select any individual column from an array result in Google Sheets.

Cons:

• Only capable of returning a single column. For multiple columns, you must use multiple Index formulas as combined (nested).
• Being a volatile function (as far as I know) the Index function causes recalculation of the formula in the cell where it resides every time Google Sheets recalculates. So it may affect the performance of the sheet if the sheet contains a very large set of data.

Now to the example formula.

Assume you want to select only the third column, i.e. the “Score 2” column from the Filter formula result. Here is the INDEX Formula for the same.

=index(
     {A1:D1;filter(A2:D9,A2:A9="Evan")}
     ,0,3
)

The number 3 in the formula controls the column position, that means the nth position.

To extract the 1st and 3rd columns, we can use multiple Index formulas together. Here is an example of this.

={
index(
     {A1:D1;filter(A2:D9,A2:A9="Evan")}
     ,0,1
),
index(
     {A1:D1;filter(A2:D9,A2:A9="Evan")}
     ,0,3
)
}

The Array_Constrain to Limit Columns

Pros:

• Shortest formula to select columns from the beginning of an array formula result in Google Sheets.
• The Array_Constrain function is specific for constraining rows as well as columns. So, as far as I’m concerned, it may bring better performance over Index.

Cons:

• Only capable of returning columns from the beginning of a range.

This function is useful for selecting n number of columns from the array output of a formula.

For example, we can extract the “Name” column with the “Score 1” column using the below ARRAY_CONSTRAIN formula. That means the first two columns of an array formula (here Filter formula) output.

=ARRAY_CONSTRAIN(
     {A1:D1;filter(A2:D9,A2:A9="Evan")}
     ,9^9,2
)

For the first 3 columns replace the last 2 with 3. Now you may know now which argument controls the nth.

Query to Select Required Column from an Array Result

Pros:

• The flexible function to select any column or any number of columns irrespective of column positions.

Cons:

• This function runs a Google Visualization API Query Language query. So use it only when you are not satisfied with the Array_Constrain.

The QUERY is the best function to constrain/extract/select n number of columns from any position of an array result in Google Sheets.

In case the above two formulas (Index and Array_Constrain) don’t meet your requirements, then use Query.

To select columns 1 and 4, use the below Query formula.

=query(
     {A1:D1;filter(A2:D9,A2:A9="Evan")},
     "Select Col1,Col4"
)

By changing the column number you can easily extract the required columns. In the above Query Col1= Column 1, Col4 = Column 4. By changing that, you can change the selection of columns.

Conclusion

In all the examples above, instead of the Filter formula as the expression, we can use any function that returns an array result. For example the GoogleFinace function.

Before winding up this Google Sheets tutorial, here is one more tip.

In all the above formulas, we must specify the columns to select, right? What about search and select one column?

For example, I want to select the column “Score 2” and I don’t know the column number. In this case, we can use the Filter function itself.

=filter(A1:D9,A1:D1="Score 2")

That’s all about selecting only the required columns in Google Sheets.

Resources:

• Extract Last Value from Each Column in Google Sheets.
• How to Get Dynamic Column Reference in Google Sheets Query.
• How to Flip a Column in Google Sheets – Finite and Infinite Columns.
• Dynamic Formula to Select Every nth Column in Query in Google Sheets.
• Remove Duplicate Rows Based on Selected Columns in Google Sheets.