A Step-By-Step Guide to Using Excel's CHOOSE Function

Guide to the CHOOSE function in Excel

Ellyot \ Unsplash

 

Excel's CHOOSE function uses an index number to find and return a specific value from a corresponding list of data. The index number indicates the position of the value in the list.

Note: The information in this article applies to Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2019 for Mac, Excel 2016 for Mac, Excel for Mac 2011, and Excel Online.

CHOOSE Function Overview

Excel CHOOSE Function

Like many of Excel's functions, CHOOSE is most effective when it is combined with other formulas or functions to return different results.

An example would be to use CHOOSE to carry out calculations using Excel's SUM, AVERAGE, or MAX functions on the same data depending upon the index number chosen.

The CHOOSE Function Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

The syntax for the CHOOSE function is:

=CHOOSE(Index_num,Value1,Value2,...Value254)

Index_num (required): Determines which value is to be returned by the function. Index_num can be a number between 1 and 254, a formula, or a reference to a cell containing a number between 1 and 254.

Value (Value1 is required. Additional values, to a maximum of 254, are optional): The list of values that is returned by the function depending upon the Index_num argument. Values can be numbers, cell references, named ranges, formulas, functions, or text.

Example Using Excel's CHOOSE Function to Find Data

Excel CHOOSE function arguments

To illustrate how to use the CHOOSE function, follow along with the example used in this tutorial. In our example, we use the CHOOSE function to calculate the yearly bonus for employees.

The bonus is a percentage of their yearly salary and the percentage is based on a performance rating between 1 and 4.

The CHOOSE function converts the performance rating into the correct percent:

 Rating 1: 3%
Rating 2: 5%
Rating 3: 7%
Rating 4: 10%

This percent value is then multiplied by the yearly salary to find the employee's yearly bonus.

The example shows how to enter the CHOOSE function into cell G2 and then use the fill handle to copy the function to cells G2 to G5.

Enter the Tutorial Data

Enter data to use CHOOSE function in Excel

Enter the following data into cells D1 to G1:

  • Cell D1: Employee
  • Cell D2: J. Smith
  • Cell D3: K. Jones
  • Cell D4: R. Johnston
  • Cell D5: L. Rogers
  • Cell E1: Rating
  • Cell E2: 3
  • Cell E3: 4
  • Cell E4: 3
  • Cell E5: 2
  • Cell F1: Salary
  • Cell F2: $50,000
  • Cell F3: $65,000
  • Cell F4: $70,000
  • Cell F5: $45,000
  • Cell G1: Bonus

Enter the CHOOSE Function

Enter the CHOOSE function in Excel

This section of the tutorial enters the CHOOSE function into cell G2 and calculates the bonus percent based on the performance rating for the first employee. These steps apply to Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel for Mac.

  1. Select cell G2. This is where the results of the function will display.
  2. Select the Formulas tab.
  3. Choose Lookup and Reference to open the function drop-down list.
  4. Select CHOOSE in the list to bring up the Function Arguments dialog box.
  5. Place the cursor in the Index_num line in the dialog box.
  6. Select cell E2 in the worksheet to enter the cell reference into the dialog box.
  7. Place the cursor in the Value1 line in the dialog box.
  8. Enter 3% on this line.
  9. Place the cursor in the Value2 line in the dialog box.
  10. Enter 5% on this line.
  11. Place the cursor in the Value3 line in the dialog box.
  12. Enter 7% on this line.
  13. Place the cursor in the Value4 line in the dialog box.
  14. Enter 10% on this line.
  15. Select OK to complete the function and close the dialog box

The value 0.07 appears in cell G2 which is the decimal form for 7%.

Excel Online does not have the Formula tab. Instead, use the Insert Function button to enter the CHOOSE function in Excel Online or any other version of Excel.

  1. Select cell G2. This is where the results of the function will display.
  2. Select Insert Function next to the Formula bar.
  3. Choose Lookup and Reference from the Category list.
  4. Select CHOOSE in the list and select OK.
  5. Type (E2,3%,5%,7%,10%) after =CHOOSE in the formula bar. Be sure to include parentheses.
  6. Press Enter.

The value 0.07 appears in cell G2 which is the decimal form for 7%.

Calculate the Employee Bonus

Calculating employee bonus with the CHOOSE function

You can now modify the CHOOSE function in cell G2 by multiplying the results of the function by the employee's annual salary to calculate his yearly bonus.

This modification is made by using the F2 key to edit the formula.

  1. Select cell G2 to make it the active cell
  2. Press F2 to place Excel in edit mode. The complete function,
    =CHOOSE(E2,3%,5%,7%,10%), appears in the cell with the insertion point located after the function's closing bracket.
  3. Type an asterisk ( * ), the multiplication symbol in Excel, after the closing bracket.
  4. Select cell F2 in the worksheet to enter the cell reference to the employee's yearly salary into the formula.
  5. Press Enter to complete the formula and to leave edit mode.
  6. The value $3,500.00 appears in cell G2, which is 7% of the employee's annual salary of $50,000.00.
  7. Select cell G2. The complete formula =CHOOSE(E2,3%,5%,7%,10%)*F2 appears in the formula bar located above the worksheet.

Copy the Employee Bonus Formula with the Fill Handle

CHOOSE function fill handle

The final step is to copy the formula in cell G2 to cells G3 to G5 using the fill handle.

  1. Select cell G2 to make it the active cell.
  2. Place the mouse pointer over the black square in the bottom right corner of cell G2. The pointer changes to a plus sign (+).
  3. Drag the fill handle down to cell G5.
  4. Cells G3 to G5 contain the bonus figures for the remaining employees.