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

Young man choosing future arrow direction
Huan Tran / Getty Images
of 02

Choosing Data with the CHOOSE Function

Excel CHOOSE Function
Excel CHOOSE Function. © Ted French

CHOOSE Function Overview

Excel's Lookup functions, which includes the CHOOSE function, are used to find and return data from a list or table based on a lookup value or index number.

In the case of CHOOSE, it 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.

For example, the function could be used to return the name of a specific month of the year based on an index number from 1 to 12 entered into the formula.

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

An example would be to have the function 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 will be 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

As can be seen in the image above, this example will use the CHOOSE function to help 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 - percent
1 3%
2 5%
3 7%
4 10%

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

The example covers entering the CHOOSE function into cell G2 and then uses the fill handle to copy the function to cells G2 to G5.

Entering the Tutorial Data

  1. Enter the following data into cells D1 to G1
  2. Employee Rating Salary Bonus
    J. Smith 3 $50,000
    K. Jones 4 $65,000
    R. Johnston 3 $70,000
    L. Rogers 2 $45,000

Entering the CHOOSE Function

This section of the tutorial enters the CHOOSE function into cell G2 and calculates bonus percent based on the performance rating for the first employee.

  1. Click on cell G2 - this is where the results of the function will be displayed
  2. Click on the Formulas tab of the ribbon menu
  3. Choose Lookup and Reference from the ribbon to open the function drop down list
  4. Click on CHOOSE in the list to bring up the function's dialog box.
  5. In the dialog box, click on the Index_num line
  6. Click on cell E2 in the worksheet to enter the cell reference into the dialog box
  7. Click on the Value1 line in the dialog box
  8. Enter 3% on this line
  9. Click on the Value2 line in the dialog box
  10. Enter 5% on this line
  11. Click on the Value3 line in the dialog box
  12. Enter 7% on this line
  13. Click on the Value4 line in the dialog box
  14. Enter 10% on this line
  15. Click OK to complete the function and close the dialog box
  16. The value " 0.07 " should appear in cell G2 which is the decimal form for 7%
of 02

CHOOSE Function Example (continued)

Click for larger image
Click for larger image. © Ted French

Calculating the Employee Bonus

This section of the tutorial modifies the CHOOSE function in cell G2 by multiplying the results of the function times the employee's annual salary to calculate his yearly bonus.

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

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

Copying the Employee Bonus Formula with the Fill Handle

This section of the tutorial copies the formula in cell G2 to cells G3 to G5 using the fill handle.

  1. Click on 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 will change to a plus sign " + "
  3. Click the left mouse button and drag the fill handle down to cell G5
  4. Release the mouse button. Cells G3 to G5 should contain the bonus figures for the remaining employees as seen in the image on page 1 of this tutorial