totn Excel Functions

MS Excel: How to use the CHOOSE Function (WS, VBA)

This Excel tutorial explains how to use the Excel CHOOSE function with syntax and examples.

Description

The Microsoft Excel CHOOSE function returns a value from a list of values based on a given position.

The CHOOSE function is a built-in function in Excel that is categorized as a Lookup/Reference Function. It can be used as a worksheet function (WS) and a VBA function (VBA) in Excel. As a worksheet function, the CHOOSE function can be entered as part of a formula in a cell of a worksheet. As a VBA function, you can use this function in macro code that is entered through the Microsoft Visual Basic Editor.

subscribe button Subscribe

If you want to follow along with this tutorial, download the example spreadsheet.

Download Example

Syntax

The syntax for the CHOOSE function in Microsoft Excel is:

CHOOSE( position, value1, [value2, ... value_n] )

Parameters or Arguments

position
The position number in the list of values to return. It must be a number between 1 and 29.
value1, value2, ... value_n
A list of up to 29 values. A value can be any one of the following: a number, a cell reference, a defined name, a formula/function, or a text value.

Returns

The CHOOSE function returns any datatype such as a string, numeric, date, etc.
If position is less than 1, the CHOOSE function will return #VALUE!.
If position is greater than the number of the number of values in the list, the CHOOSE function will return #VALUE!.

Note

  • If position is a fraction (not an integer value), it will be converted to an integer by dropping the fractional component of the number.

Applies To

  • Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function

  • Worksheet function (WS)
  • VBA function (VBA)

Example (as Worksheet Function)

Let's look at some Excel CHOOSE function examples and explore how to use the CHOOSE function as a worksheet function in Microsoft Excel:

Excel CHOOSE function

Based on the Excel spreadsheet above, the following CHOOSE examples would return:

=CHOOSE(1, A2, A3, A4, A5)
Result: "TechOnTheNet.com"

=CHOOSE(2, A2, A3, A4, A5)
Result: "DigMinecraft.com"

=CHOOSE(3, A2, A3, A4, A5)
Result: "CheckYourMath.com"

=CHOOSE(1, "Tech", "on", "the", "Net")
Result: "Tech"

=CHOOSE(5, A2, A3, A4, A5)
Result: #VALUE!              'The position parameter is greater than the number of items in the list so #VALUE! error is returned

=CHOOSE(1.2, A2, A3, A4, A5)
Result: "TechOnTheNet.com"   'Returns the value from position 1 in the list because the fractional portion is dropped

Example (as VBA Function)

The CHOOSE function can also be used in VBA code in Microsoft Excel.

Let's look at some Excel CHOOSE function examples and explore how to use the CHOOSE function in Excel VBA code:

Dim LValue As String

LValue = Choose(1, "Tech", "on", "the", "Net")

In this example, the variable called LValue would contain "Tech" as a value.

Frequently Asked Questions

Question: In Microsoft Excel, my question concerns formatting numbers in a particular cell. For example, the cell says:

="Gas price: $" & CHOOSE(gas.deck, B1, B2, B3) & "per mmbtu"

But returns $3 when the cell is formatted to two decimals. If the price were $3.25, the correct price would show. Any help would be greatly appreciated.

Answer: Even though your cell is formatted with a number format, your formula is returning text not a numeric value so the number format will not be applied. You will need to apply the format to the number inside of the formula.

You could try using the DOLLAR function to apply the format as follows: (you will need to remove your $ sign because the DOLLAR function will insert one automatically)

="Gas price: " & DOLLAR(CHOOSE(gas.deck, B1, B2, B3)) & "per mmbtu"

This formula should now return something like:

Gas price: $3.25per mmbtu
Share on: