CHOOSE function

CHOOSE returns the value or range from a list of arguments that corresponds to the supplied index number, letting you pick one of several options.

=CHOOSE(index_num, value1, ...)

Generate a CHOOSE formula

Describe what you need. The generator will reach for CHOOSE where CHOOSE is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How CHOOSE reads its arguments
index_numrequiredvalue1requiredCHOOSE →
ArgumentRequiredDescription
index_numRequiredRequired numeric argument; must be a positive integer. If it exceeds the number of supplied values, CHOOSE returns #N/A.
value1RequiredRequired first value; can be a constant, text, number, or cell/range reference. All subsequent values must be of compatible type for consistent results.
...RepeatingOptional additional values; repeat as needed. Each extra argument expands the list of selectable items.

Returns

A single value or an array, matching the shape of the chosen argument.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Select a performance metric column

CampaignChannelSpendClicksConversions
Spring SaleEmail5000120080
Spring SaleSocial3000150065
Summer PromoSearch70002000150
Summer PromoDisplay4000180090
=CHOOSE(2, C2:C5, D2:D5, E2:E5)

Result: [1200;1500;2000;1800]

The index_num 2 tells CHOOSE to return the second argument, which is the D2:D5 range containing Clicks. The function therefore outputs the Clicks column as a vertical array. Because the chosen range is the same size as the others, the result keeps the original row order.

2. Lookup spend for a specific channel

CampaignChannelSpendClicksConversions
Spring SaleEmail5000120080
Spring SaleSocial3000150065
Summer PromoSearch70002000150
Summer PromoDisplay4000180090
=CHOOSE(MATCH("Social",B2:B5,0), C2, C3, C4, C5)

Result: 3000

MATCH finds the position of "Social" in the Channel column, returning 2. CHOOSE then picks the second value argument, C3, which holds the spend of 3000 for the Social channel. This demonstrates how CHOOSE can be combined with lookup functions for dynamic selection.

3. Create a custom label for the first row

CampaignChannelSpendClicksConversions
Spring SaleEmail5000120080
=CHOOSE(1, "Spend: "&C2, "Clicks: "&D2, "Conversions: "&E2)

Result: Spend: 5000

The index_num 1 selects the first argument, which concatenates the label "Spend: " with the value in C2. The result is the text string "Spend: 5000". Changing the index to 2 or 3 would return analogous labels for Clicks or Conversions.

Common errors

Which CHOOSE error are you seeing?
CHOOSE returned an error#N/A
Reduce the index to a number that exists in the list, or add more value arguments so the index becomes valid.
#VALUE!
Convert the index to a number (e.g., using VALUE or INT) and ensure it is at least 1.
#REF!
Repair the broken reference by restoring the range or removing the invalid argument from the CHOOSE call.
ErrorWhy it happensHow to fix it
#N/AThe index_num is larger than the count of value arguments, so CHOOSE cannot find a matching item.Reduce the index to a number that exists in the list, or add more value arguments so the index becomes valid.
#VALUE!The index_num is non-numeric or zero; CHOOSE requires a positive integer.Convert the index to a number (e.g., using VALUE or INT) and ensure it is at least 1.
#REF!One of the value arguments refers to a range that no longer exists, such as a deleted column or row.Repair the broken reference by restoring the range or removing the invalid argument from the CHOOSE call.

Tips and when to use something else

  • CHOOSE works best when you have a small, fixed list of options; for larger dynamic selections, consider INDEX combined with MATCH.
  • The index_num is truncated to an integer, so decimal values are rounded down—watch out for unintended results.
  • You can nest CHOOSE inside data-validation lists to let users pick from a custom set of labels.
  • When you need to pull whole columns by position, CHOOSECOLS is more concise and maintains array-aware behavior.

Related functions

VLOOKUPVLOOKUP searches the first column of a table and returns a value from a specified column in the same row, optionally using exact or approximate matching.XLOOKUPXLOOKUP returns the value from a specified return array that corresponds to a given lookup value, with optional handling for missing matches and search direction.INDEXINDEX returns the value or array of values from a specified position within a given range, based on row and optional column numbers.MATCHMATCH returns the relative position of a lookup value within a one-dimensional range, optionally using exact, less-than, or greater-than matching.CHOOSECOLSCHOOSECOLS returns the selected columns from a given array, preserving order and data types, and spills the result into adjacent cells.

Frequently asked questions

How do I use CHOOSE to return a whole row instead of a column?
Place the row ranges as separate arguments and set index_num to the row position you want. For example, =CHOOSE(3, A2:E2, A3:E3, A4:E4) returns the third row of the table.
Can CHOOSE be used with arrays in Google Sheets?
Yes. In Google Sheets, CHOOSE can return array results when you pass range arguments. The function will spill the chosen range into adjacent cells, just like any other array-returning formula.
Why does CHOOSE sometimes give #N/A even though my index looks correct?
CHOOSE treats any non-integer index as the integer part of the number. If your index is a formula that can evaluate to a fraction or an error, the truncated value may fall outside the argument list, producing #N/A. Ensure the index is a clean integer.
When should I prefer XLOOKUP over CHOOSE for selecting a column?
XLOOKUP is designed for lookup-by-value scenarios and can return whole columns based on a matching key, handling missing keys gracefully. Use CHOOSE when you simply need to pick one of a few known columns by position; use XLOOKUP when the column to return depends on a data-driven condition.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator