Summary

To combine ranges or arrays horizontally, you can use the CHOOSE function with an array constant. In the example shown, the formula in cell G5 is:

=CHOOSE({1,2},B5:B16,D5:D16)

The result is that the range B5:B16 and range D5:D16 are joined together horizontally into a single range.

In Excel 2024+ and Excel 365, the HSTACK function makes this use of CHOOSE unnecessary. For more on how the CHOOSE version works, and what can go wrong, see Combine columns with an array of index numbers.

Generic formula

=CHOOSE({1,2},range1,range2)

Explanation

In this example, the goal is to join two one-dimensional ranges together horizontally. This can be done with the CHOOSE function and array constant.

The CHOOSE function

The CHOOSE function is used to select arbitrary values by numeric position. CHOOSE is a flexible function and accepts a list of text values, numbers, cell references, in any combination. For example, if we have the colors "red", "blue", and "green", we can use CHOOSE like this:

=CHOOSE(1,"red", "blue", "green") // returns "red"
=CHOOSE(2,"red", "blue", "green") // returns "blue"
=CHOOSE(3,"red", "blue", "green") // returns "green"

If we give CHOOSE an array constant like {1,2}, CHOOSE will return the first and second values in an array at the same time:

=CHOOSE({1,2}},"red", "blue", "green") // returns {"red","blue"}

The result is an array that contains two values and, in the dynamic array version of Excel, these values spill onto the worksheet into the range G5:H16.

Applications

Traditionally, the use of CHOOSE function to combine ranges is used up in tricky array formulas. The formulas below are good examples:

In these formulas, the CHOOSE function is used to create a new table (in memory) that can be used by the VLOOKUP function to work around a difficult problem.

Support Exceljet

Many websites (including Exceljet) have lost over half their traffic in the past year because of the crushing impact of AI on web searches. Exceljet is still at it, publishing free content each week. If you find Exceljet helpful, you can support the site and go ad-free with the Exceljet Lifetime Pass. One payment, no subscription, and ads are gone forever. Plus, you get our Excel Shortcuts video course as a thank you.

Get the Lifetime Pass

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.