keepcalculator

06 Numbers and Powers

Combine Ranges with Choose? How It Works

Card No. 06·73329 · Updated · Standard formula, shown below

Yes, you can combine ranges with the CHOOSE function in Excel and Google Sheets, but not by listing them directly inside CHOOSE. Instead, you use the CHOOSE function to pick which range to feed into another function, or you use it with functions like VLOOKUP, SUM, or AVERAGE to switch between different data sets. This article explains exactly how to do it, what the limitations are, and how to work around them.

What CHOOSE Actually Does

CHOOSE is a simple function: it takes an index number and a list of values, then returns the value at that index. The syntax is:

CHOOSE(index_num, value1, [value2], ...)

For example, =CHOOSE(2, "Red", "Green", "Blue") returns “Green” because it is the second value. The index must be between 1 and the number of values you provide. If you give it 3 values, the index can only be 1, 2, or 3. Anything else returns a #VALUE! error.

Now, the key point: each value in the list can be a range reference, not just a single cell or text. So =CHOOSE(1, A1:A10, B1:B10) returns the entire range A1:A10. But here is the catch—if you put that formula in a single cell, it will spill the whole range if you are using Excel 365 or Excel 2021. In older Excel, you would need to enter it as an array formula (Ctrl+Shift+Enter). In Google Sheets, it will spill automatically as well.

How to Combine a Range with CHOOSE

The most common way to combine a range with CHOOSE is to use CHOOSE as the lookup array inside another function. For instance, you want to sum one of several columns based on a dropdown selection. You could write:

=SUM(CHOOSE(B1, A2:A100, B2:B100, C2:C100))

Here, B1 contains the index (1, 2, or 3). If B1 is 2, CHOOSE returns B2:B100, and SUM adds that column. This works in Excel 365, Excel 2021, and Google Sheets. In older Excel versions, you must press Ctrl+Shift+Enter after typing the formula so it becomes an array formula.

Another common pattern is using CHOOSE with VLOOKUP to pick which table to search:

=VLOOKUP(D1, CHOOSE(E1, Table1, Table2), 2, FALSE)

This lets you switch between two named ranges based on a cell value. It is a clean way to avoid nested IF statements.

Limitations: Why You Cannot Just Write =CHOOSE(1, A1:A10, B1:B10) in One Cell

If you try to put =CHOOSE(1, A1:A10, B1:B10) in a single cell, you will get a #VALUE! error in older Excel because a single cell cannot hold multiple values. In Excel 365 and Google Sheets, it will spill the values down, which may be what you want, but it can also overwrite existing data if there is something below. To avoid that, you need to either:

  • Use the formula in a context that expects an array, like inside SUM, AVERAGE, or VLOOKUP.
  • Use the INDEX function, which can return a range without spilling.
  • Use the IFS or SWITCH function for simpler logic.

Better Alternatives: INDEX, IFS, and SWITCH

For many cases, INDEX is more flexible than CHOOSE. For example, to return a range from a list of ranges, you can use:

=INDEX((A1:A10, B1:B10, C1:C10), 0, 1, 2)

This returns the entire second range (B1:B10) because the row argument is 0 (meaning whole column), column is 1, and area is 2. This works in Excel and Google Sheets without spilling issues when used inside another function.

IFS and SWITCH are better when you need to pick a value based on a condition rather than a numeric index. For example, =IFS(A1="Jan", B2:B10, A1="Feb", C2:C10) returns the appropriate range. SWITCH is similar but uses equality matching.

Common Mistakes and How to Avoid Them

One frequent mistake is forgetting that CHOOSE requires the index to be a number, not text. If your dropdown contains “Option 1” as text, you need to convert it to a number with VALUE() or use MATCH. Another mistake is using CHOOSE with ranges of different sizes—if you try to combine A1:A10 with B1:B5, the resulting array will have mismatched dimensions, causing errors in functions like SUM. Always ensure the ranges have the same number of rows and columns.

Also, in older Excel, you must confirm array formulas with Ctrl+Shift+Enter. If you forget, you will get a #VALUE! error or only the first value. In Excel 365 and Google Sheets, dynamic arrays handle this automatically, but you still need to be aware of spilling.

Real-World Example: Switching Between Monthly Sales Data

Suppose you have sales data for January, February, and March in columns B, C, and D, rows 2 to 100. You want to calculate the average sales for a month selected in cell F1 (which contains 1, 2, or 3). The formula would be:

=AVERAGE(CHOOSE(F1, B2:B100, C2:C100, D2:D100))

If F1 is 2, it averages C2:C100. This is much cleaner than writing =IF(F1=1, AVERAGE(B2:B100), IF(F1=2, AVERAGE(C2:C100), AVERAGE(D2:D100))).

Another example: you have two price lists—one for retail, one for wholesale. You want to look up a product’s price from either list based on a cell that says “Retail” or “Wholesale”. You could use:

=VLOOKUP(A2, CHOOSE(MATCH(B1, {"Retail","Wholesale"},0), RetailPrices, WholesalePrices), 2, FALSE)

This uses MATCH to convert the text to an index, then CHOOSE to select the correct named range.

Frequently Asked Questions

Can I use CHOOSE with a range in Google Sheets?

Yes, Google Sheets supports CHOOSE with ranges. It will spill the results if used alone, but it works perfectly inside other functions like SUM or AVERAGE. The syntax is the same as Excel.

Why does my CHOOSE formula return #VALUE! when I use a range?

This usually happens in older Excel versions when you enter the formula in a single cell without Ctrl+Shift+Enter. It can also happen if your index number is out of range, or if the ranges have different dimensions. Check those three things.

Is there a way to combine multiple ranges into one without CHOOSE?

Yes, you can use the VSTACK function in Excel 365 and Google Sheets to stack ranges vertically, or HSTACK to stack them horizontally. For example, =VSTACK(A1:A10, B1:B10) combines them into one column. This is often simpler than CHOOSE if you want to combine all ranges, not pick one.

Can I use CHOOSE to return a range that I can then edit?

No, CHOOSE returns a value or array, not a reference that you can edit directly. If you need to edit a range based on a selection, you would need to use INDIRECT or a macro. INDIRECT can convert a text string like “Sheet2!A1:A10” into a reference, but it is volatile and can be slow in large workbooks.

What is the difference between CHOOSE and INDEX for ranges?

CHOOSE picks from a list of values you explicitly type out. INDEX can pick from a range of ranges, which is useful when you have many ranges and want to use a number to select one. INDEX is also more efficient for large data sets because it does not create an array of all values.

Does CHOOSE work with named ranges?

Yes, you can use named ranges as the values inside CHOOSE. For example, =CHOOSE(1, SalesJan, SalesFeb) works if SalesJan and SalesFeb are named ranges. This makes your formulas more readable.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.

More cards in this drawer

The whole Numbers and Powers drawer →