ThisCalculator

Everyday

How to Use the CHOOSE Function

Updated · Standard formula, shown below

The CHOOSE function in Excel and Google Sheets returns a value from a list based on a position number you supply. It is useful for simple lookups, assigning categories, and building flexible formulas. This guide explains the syntax, shows examples, and covers common pitfalls.

What You’ll Need

  • A spreadsheet application (Microsoft Excel, Google Sheets, LibreOffice Calc, or similar) that supports the CHOOSE function.
  • A basic understanding of cell references and formulas.
  • Your data ready in a sheet, or a clear idea of the list of values you want to choose from.

Step-by-Step

  1. Understand the syntax. The CHOOSE function has two parts: CHOOSE(index_num, value1, [value2], ...). The index_num is a number that tells which value to return. For example, CHOOSE(2, "Red", "Blue", "Green") returns Blue because Blue is the second value.
  2. Start typing the formula. Click the cell where you want the result. Type =CHOOSE(. Your spreadsheet will show a tooltip with the arguments.
  3. Enter the index number. This can be a number typed directly, a cell reference (like A1), or another formula that results in a number. For example, if cell B2 contains the number 3, then CHOOSE(B2, ...) will pick the third value.
  4. List the values. After the index number, add a comma, then type each value separated by commas. Values can be numbers, text (in quotes), cell references, or even formulas. For instance: =CHOOSE(A1, "Low", "Medium", "High").
  5. Close the parentheses and press Enter. The formula will evaluate and display the chosen value. If you used a cell reference for the index, the result updates automatically when that cell changes.
  6. Use CHOOSE for simple lookups. For example, to assign a day name to a number 1–7, use =CHOOSE(A2, "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"). This is a common use case.
  7. Nest CHOOSE for more complex logic. You can put a CHOOSE inside another CHOOSE, or combine it with IF, VLOOKUP, or other functions. For example, =CHOOSE(IF(A1>0,1,2), "Positive", "Non-positive").
  8. Use CHOOSE with other functions. You can use CHOOSE to select a calculation. For instance, =CHOOSE(B1, A1+A2, A1-A2, A1*A2) will add, subtract, or multiply depending on the value in B1.

Common Mistakes

  • Index out of range. If the index number is less than 1 or greater than the number of values, you get a #VALUE! error. Always ensure your index is valid.
  • Using 0 as the index. CHOOSE does not have a zero-based index. CHOOSE(0, "A", "B") returns an error. The first value is always index 1.
  • Forgetting commas. Each value must be separated by a comma. Missing a comma breaks the formula.
  • Using text that looks like numbers. If you want to return a number, type it without quotes. If you type “5” (with quotes), it becomes text, which may affect calculations.
  • Using CHOOSE for large ranges. CHOOSE is limited to a maximum of 254 values in Excel (older versions had fewer). For large lookups, use VLOOKUP or INDEX/MATCH instead.

Tips

  • Use cell references for values. Instead of typing values directly, reference cells so you can update them easily. Example: =CHOOSE(A1, B1, B2, B3).
  • Combine with ROW or COLUMN for dynamic lists. For example, to pick a value based on the row number, use =CHOOSE(ROW(), "First", "Second", "Third").
  • Use CHOOSE to reorder columns. You can use it in array formulas to reorder data, though this can be complex.
  • Keep the index in a separate cell. This makes it easy to change the selection without editing the formula.
  • Use CHOOSE for small lookup tables. It is a lightweight alternative to VLOOKUP when you have only a few items.

Troubleshooting

  • #VALUE! error: This usually means the index number is not valid (less than 1, greater than the number of values, or not a number). Check the index cell and the number of values.
  • #NAME? error: This often means the function name is misspelled or your spreadsheet does not support CHOOSE (unlikely in modern versions). Check the spelling.
  • #REF! error: This can occur if a value argument refers to a deleted cell or range. Check the references.
  • Unexpected result: If the formula returns the wrong value, verify the index number. Remember that the index is 1-based, and the order of values matters.
  • Formula not updating: If you change the index cell but the result stays the same, ensure calculation is set to automatic (in Excel: Formulas > Calculation Options > Automatic).

Frequently Asked Questions

Can CHOOSE return a range of cells?

Yes, you can use a range as a value. For example, =CHOOSE(1, A1:A5) returns the range A1:A5, but you usually need to enter it as an array formula or use it within another function like SUM.

What is the maximum number of values CHOOSE can handle?

In modern Excel, the limit is 254 values. Google Sheets has a similar limit. If you need more, consider using a different lookup function.

Can I use CHOOSE with a drop-down list?

Yes, you can reference a cell that contains a data validation drop-down. For example, if the drop-down in A1 shows 1, 2, or 3, then CHOOSE(A1, …) will work.

Is CHOOSE faster than VLOOKUP?

For small lists, CHOOSE is often faster because it does not search a table. For large tables, VLOOKUP or INDEX/MATCH may be more efficient.

Can I use CHOOSE to return a formula?

Yes, you can put formulas as value arguments. For example, =CHOOSE(1, SUM(A1:A10), AVERAGE(A1:A10)) returns the sum if index is 1, the average if index is 2.

Does CHOOSE work in Google Sheets?

Yes, the syntax and behavior are identical to Excel.

Related

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