Mastering the Excel CHOOSE Function: Syntax, Examples, and VBA Script

Want to improve your Excel skills? Learning the CHOOSE function is a great step. It helps you pick a value from a list based on an index number. This is perfect for making dynamic formulas or dashboards. In this blog, we'll dive into how the Excel CHOOSE function works, give you examples, and show you how to use it in VBA.

Table of Contents

What is the CHOOSE Function in Excel?

The CHOOSE function in Excel picks a value from a list based on an index number. It's great for selecting values manually or through code.

Syntax:

CHOOSE(index_num, value1, [value2], ...)

index_num – A number that tells which value to return.
value1, value2, … – The list of values to choose from.

Key Points:

The index_num must be between 1 and the number of values listed.
You can use cell references, text, numbers, or formulas in the values.

Real-Life Examples of the CHOOSE Function

Example 1: Basic Usage

=CHOOSE(2, "Apple", "Banana", "Cherry")

Result: Banana
Why? Because the index is 2, and "Banana" is the second item in the list.

Example 2: Using CHOOSE with WEEKDAY

You can use CHOOSE with other functions. For example, mapping the WEEKDAY function to week names:

=CHOOSE(WEEKDAY(A1), "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")

Result: Returns the day name based on the date in cell A1.

Example 3: CHOOSE with RANDBETWEEN for Random Selection

=CHOOSE(RANDBETWEEN(1,3), "Yes", "No", "Maybe")

Result: Randomly returns "Yes", "No", or "Maybe".

Using the CHOOSE Function in VBA

Want to use CHOOSE in your macros? Excel VBA supports the Choose function (note the lowercase spelling).

VBA Example:

Sub ChooseExample()
    Dim result As String
    Dim index As Integer

    index = 3 ' you can change this dynamically
    result = Choose(index, "Low", "Medium", "High")

    MsgBox "The selected priority is: " & result
End Sub

This VBA script shows a message box with "The selected priority is: High" if the index is 3.

Why Use CHOOSE Instead of IF or SWITCH?

Simplicity: Cleaner syntax than multiple IF statements.
Performance: Slightly faster for short lists.
Compatibility: Works in all Excel versions (unlike SWITCH which is available only in Excel 2016+).

Final Thoughts

The CHOOSE function in Excel is a powerful tool for making dynamic decisions. It simplifies formulas and makes your spreadsheets smarter. Whether you're creating interactive dashboards or automating decisions with VBA, CHOOSE can be a real game-changer.

External Resource Links:

Microsoft Official Documentation – CHOOSE Function
- https://support.microsoft.com/en-us/excel-choose-function
Excel Jet – CHOOSE Function Overview
- https://exceljet.net/excel-functions/excel-choose-function
Contextures – Advanced Excel Formulas Using CHOOSE
- https://www.contextures.com/excelchoosefunction.html
Excel Campus – Excel Formula Training (including CHOOSE)
- https://www.excelcampus.com/functions/
MrExcel Forum – Community Q&A on CHOOSE Function
- https://www.mrexcel.com/board/tags/choose-function/