Returns a value from a list based on an index number. CHOOSE acts like a lookup table with sequential indices. Essential for conditional selection, dynamic formulas, and creating switch-like behavior.
Master the fundamentals of Excel CHOOSE function
The CHOOSE function returns a value from a list of values based on an index number. It's a simple lookup mechanism where index 1 returns the first value, index 2 returns the second, etc. CHOOSE is particularly useful for creating conditional formulas, replacing nested IF statements, and implementing switch-like behavior in Excel.
Index starts at 1, not 0
Can handle up to 254 value parameters
Index can be calculated or come from cell
Values can be numbers, text, references, or formulas
Function-specific parameters
Function-specific return type
Select values based on calculated index
Simplify nested IF statements with CHOOSE
Return different calculations based on index
Map numbers to text or other values
Exact matching required
Returns numeric position
Handles missing text gracefully
=CHOOSE(index_num, value1, value2, value3...)Specifies which value to return. Must be between 1 and 254. 1 returns the first value, 2 returns the second, etc.
The first value from which to choose. Can be a number, text, cell reference, or formula.
The second value from which to choose.
Additional values up to value254. CHOOSE can handle up to 254 values.
The value at the specified index position
Description: Returns a value from a list of values based on an index number (1-based)
Select value by index
Returns the second value (index 2) from the list: "Banana". Index is 1-based.
Use CHOOSE function in VBA to select values
' Basic CHOOSE in VBA
Range("B1").Value = Application.WorksheetFunction.Choose(2, "Apple", "Banana", "Cherry")
' Returns: Banana
' CHOOSE with cell values
Dim index As Integer
index = Range("A1").Value
Range("B2").Value = Application.WorksheetFunction.Choose(index, Range("C1"), Range("C2"), Range("C3"))
' Dynamic selection based on condition
Sub SelectValueByCondition()
Dim value As Double
value = Range("A1").Value
Dim result As Variant
If value > 100 Then
result = Application.WorksheetFunction.Choose(1, "High", "Medium", "Low")
ElseIf value > 50 Then
result = Application.WorksheetFunction.Choose(2, "High", "Medium", "Low")
Else
result = Application.WorksheetFunction.Choose(3, "High", "Medium", "Low")
End If
Range("B1").Value = result
End Sub
' CHOOSE with calculated index
Sub UseChooseWithCalculation()
Dim dayNum As Integer
dayNum = Weekday(Date)
Dim dayName As String
dayName = Application.WorksheetFunction.Choose(dayNum, "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat")
Range("A1").Value = dayName
End SubSelect values based on calculated index
Convert day number to day name
Return different calculations based on selection
Select from predefined options by index
CHOOSE returns #VALUE! when index_num is not numeric or out of range
=CHOOSE(MAX(1, MIN(254, A1)), ...)Solution: Ensure index_num is between 1 and the number of values provided. Check for text values, negative numbers, or zero. Index must be integer between 1 and 254.
CHOOSE returns different value than expected
=CHOOSE(2, "First", "Second", "Third") returns "Second"Solution: Remember CHOOSE uses 1-based indexing. Index 1 = first value, index 2 = second value, etc. Verify index calculation is correct.
Index exceeds number of values provided
=CHOOSE(MIN(index, 254), value1, value2, ...)Solution: Ensure index_num is not greater than the number of value parameters. Use IF or MIN to limit index to valid range.
CHOOSE with decimal index (e.g., 1.5)
=CHOOSE(ROUND(index, 0), ...)Solution: CHOOSE truncates decimal indices. 1.5 becomes 1, 2.7 becomes 2. Use ROUND or INT if you need specific rounding behavior.