To convert/move a single column to multiple columns, we can adopt different methods in Google Sheets.

Earlier I’ve shared two tutorials on the same or similar topic.

In the first one, I have used the Skipping (Clause) in Query, and in the other one, I’ve used the Split function.

Here are them.

• How to Move Each Set of Rows to Columns in Google Sheets.
• Split a Column into Multiple N Columns In Google Sheets.

But this time, we can learn how to move values in a single column to multiple columns using the Hlookup function in Google Sheets.

It seems more flexible than the other two.

To achieve our goal of converting single column values to multiple column values, I’m deviating a little bit from the real purpose of the Hlookup function, which is horizontal lookup, here.

Here also, we are using the Hlookup function for horizontal lookup. But our purpose is to move a single column value to multiple columns.

Let’s see how to move a single column value to multiple columns of our choice (‘N’) in Google Sheets.

For example, in the range A2:A, I have the month names from January to December.

I want to move these month names to 3 columns as below.

Let me take you to the solution.

Hlookup to Move Single Column to Multiple Columns

There are month names in A2:A.

I want to move these month names to 3 columns.

Here is the Hlookup formula (in cell C2 as per the image above) to do that.

Formula # 1:

=ArrayFormula(
     IFERROR(
        hlookup(
           "Test",
           {"Test";A2:A},
           SEQUENCE(roundup(COUNTA(A2:A)/3),3,2),
           0
        )
     )
)

You can do the same with any other values like strings, numbers, dates, or even mixed-type data in a single column.

Though Hlookup is the main formula, the Sequence function is equally important here.

In my formula above, it acts as the Hlookup ‘index’. How?

Syntax:

HLOOKUP(search_key, range, index, [is_sorted])

See the logic and formula explanation below.

Logic and Explanation

You won’t be able to correctly read my above formula if you are new to Hlookup. So you may try to understand it first.

Here is one example. The data range in use is A1:C4.

Hlookup searches across the first row of a range, right?

Assume I want to search across row # 1 for the value “Area 1” and return all the values (5, 10, and 15) from the found column.

For this, we can use Hlookup as below.

Formula # 2:

=ArrayFormula(
     hlookup(
        "Area 1",
        A1:C4,
        {2;3;4},
        0
     )
)

In this {2;3;4} are the ‘index’ row (output row) numbers.

Please note that the first row in ‘range’ (A1:C4) is numbered 1. But I haven’t used {1;2;3;4} as the ‘index’ since I don’t want the value from the first row, i.e., the search key “Area 1”.

That means, by specifying row numbers as an array in the index, we can extract values from any rows using Hlookup.

I’ve used the same logic to convert/move a single column value to multiple column values in Google Sheets.

There I’ve specified the index row numbers as per my requirement that as a multiple columns array. That’s the logic part.

Now to the formula (Formula # 1) explanation.

Things I’ve Done

Our actual range is A2:A. I’ve changed it as {"Test";A2:A} which is the ‘range’ in Hlookup.

Since the first row of this range contains “Test,” the ‘search_key’ used in Hlookup is “Test,”

So that Hlookup can return values from this column (A2:A) as per our requirement (‘index’ array/matrix).

Now the Sequence part, the ‘index’ in Hlookup, is a combination formula.

SEQUENCE(roundup(COUNTA(A2:A)/3),3,2)

The COUNTA(A2:A) part in this formula returns the count of the values in A2:A.

Since we want to move the values from column A (single column) to 3 columns (multiple columns), I have divided the count result by 3. Then round up the number.

That means 12/3 = 4. So the result will be in 4 rows.

The rounding of the number is necessary to avoid issues in the case of decimal places after division.

As per Sequence syntax, the above formula output is the number of rows (4 rows) to return.

SEQUENCE(rows, [columns], [start], [step])

We want to move a single column value to 3 columns. So we should specify 3 as ‘columns’ in Sequence.

The ‘start’ value (the third argument in Sequence) must be 2 as the first value in the range is “Test,” which we want to omit.

The output of the above Sequence formula would be a 4 x 3 matrix as below.

The above, the values highlighted in yellow, is the ‘index’ in Hlookup up.

That’s why the formula returns values from rows 2, 3, 4 in one row, 5, 6, 7 in another row, and so on.

Moving Single Column to Multiple N Columns and Defining N

To move a single column value to 5 columns, change the number 3 (all) in the above formula to 5. That’s all!

This way, we can move single column values to multiple columns in Google Sheets. Thanks for the stay. Enjoy!