Quick, clean, and to the point

Combine ranges with CHOOSE

Excel formula: Combine ranges with CHOOSE
Generic formula 

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:


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


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.


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 workaround a difficult problem.

Note: the forthcoming HSTACK function will make this use of CHOOSE unnecessary.

Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.