Excel CHOOSE Function
The Excel CHOOSE function returns a value from a range (list) that is driven by a specified position
Example: Excel CHOOSE Function
|
=CHOOSE(1,"walk","run","stop","sprint")
|
Result in cell G5 (walk) - returns the first position from the specified range (list).
|
|
=CHOOSE(2,"walk","run","stop","sprint")
|
Result in cell G6 (run) - returns the second position from the specified range (list).
|
|
=CHOOSE(3,"walk","run","stop","sprint")
|
Result in cell G7 (stop) - returns the third position from the specified range (list).
|
|
=CHOOSE(4,"walk","run","stop","sprint")
|
Result in cell G8 (sprint) - returns the fourth position from the specified range (list).
|
METHOD 2. Excel CHOOSE function using links
EXCEL
|
=CHOOSE(B5,C5,D5,E5,F5)
|
Result in cell G5 (walk) - returns the first position from the specified range (list).
|
|
=CHOOSE(B6,C6,D6,E6,F6)
|
Result in cell G6 (run) - returns the second position from the specified range (list).
|
|
=CHOOSE(B7,C7,D7,E7,F7)
|
Result in cell G7 (stop) - returns the third position from the specified range (list).
|
|
=CHOOSE(B8,C8,D8,E8,F8)
|
Result in cell G8 (sprint) - returns the fourth position from the specified range (list).
|
METHOD 3. Excel CHOOSE function using the Excel built-in function library with hardcoded values
EXCEL
Formulas tab > Function Library group > Lookup & Reference > CHOOSE > populate the input boxes
| =CHOOSE(1,"walk","run","stop","sprint") Note: in this example we are populating the Index_num and four value input boxes, however, you are only required to populate the required arguments (Index_num and at least one value). |
 |
METHOD 4. Excel CHOOSE function using the Excel built-in function library with links
EXCEL
Formulas tab > Function Library group > Lookup & Reference > CHOOSE > populate the input boxes
| =CHOOSE(B5,C5,D5,E5,F5) Note: in this example we are populating the Index_num and four value input boxes, however, you are only required to populate the required arguments (Index_num and at least one value). |
 |
Sub Excel_CHOOSE_Function_Using_Hardcoded_Values()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("CHOOSE")
'apply the Excel CHOOSE function
ws.Range("G5") = Choose(1, "walk", "run", "stop", "sprint")
ws.Range("G6") = Choose(2, "walk", "run", "stop", "sprint")
ws.Range("G7") = Choose(3, "walk", "run", "stop", "sprint")
ws.Range("G8") = Choose(4, "walk", "run", "stop", "sprint")
ws.Range("G5") = Choose(1, "walk", "run", "stop", "sprint")
ws.Range("G6") = Choose(2, "walk", "run", "stop", "sprint")
ws.Range("G7") = Choose(3, "walk", "run", "stop", "sprint")
ws.Range("G8") = Choose(4, "walk", "run", "stop", "sprint")
End Sub
OBJECTS
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
PREREQUISITES
Worksheet Name: Have a worksheet named CHOOSE.
Worksheet Name: Have a worksheet named CHOOSE.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("G5"), ("G6"), ("G7") and ("G8") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
METHOD 2. Excel CHOOSE function using VBA with links
VBA
Sub Excel_CHOOSE_Function_Using_Links()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("CHOOSE")
'apply the Excel CHOOSE function
ws.Range("G5") = Choose(Range("B5").Value, Range("C5").Value, Range("D5").Value, Range("E5").Value, Range("F5").Value)
ws.Range("G6") = Choose(Range("B6").Value, Range("C6").Value, Range("D6").Value, Range("E6").Value, Range("F6").Value)
ws.Range("G7") = Choose(Range("B7").Value, Range("C7").Value, Range("D7").Value, Range("E7").Value, Range("F7").Value)
ws.Range("G8") = Choose(Range("B8").Value, Range("C8").Value, Range("D8").Value, Range("E8").Value, Range("F8").Value)
ws.Range("G5") = Choose(Range("B5").Value, Range("C5").Value, Range("D5").Value, Range("E5").Value, Range("F5").Value)
ws.Range("G6") = Choose(Range("B6").Value, Range("C6").Value, Range("D6").Value, Range("E6").Value, Range("F6").Value)
ws.Range("G7") = Choose(Range("B7").Value, Range("C7").Value, Range("D7").Value, Range("E7").Value, Range("F7").Value)
ws.Range("G8") = Choose(Range("B8").Value, Range("C8").Value, Range("D8").Value, Range("E8").Value, Range("F8").Value)
End Sub
OBJECTS
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
PREREQUISITES
Worksheet Name: Have a worksheet named CHOOSE.
Worksheet Name: Have a worksheet named CHOOSE.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("G5"), ("G6"), ("G7") and ("G8") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
DESCRIPTION
The Excel CHOOSE function returns a value from a range (list) that is driven by a specified position.
The Excel CHOOSE function returns a value from a range (list) that is driven by a specified position.
SYNTAX
=CHOOSE(index_num, value1, [value2], …)
=CHOOSE(index_num, value1, [value2], …)
ARGUMENTS
index_num: (Required) The position number of the value to be returned.
value1: (Required) A value that can be called upon. A value can be a number, reference to a rage, defined name, formula, function or text.
index_num: (Required) The position number of the value to be returned.
value1: (Required) A value that can be called upon. A value can be a number, reference to a rage, defined name, formula, function or text.
ADDITIONAL NOTES
Note 1: The index_num argument can be populated with a number between 1 and 254 for Excel 2007 and later, or maximum of 29 values for Excel 2003 and earlier.
Note 2: In Excel 2007 and later the CHOOSE function can accept up to 254 value arguments. In Excel 2003 the CHOOSE function can only accept up to 29 value arguments.