CHOOSE Function in Excel Tutorial

Excel Tutorial

Introduction

The CHOOSE function in Excel helps you select a value from a list of options using a position number. It is useful when you want Excel to return a specific department, month, grade, product, or other value based on a number. In this tutorial, you will learn the CHOOSE function from the basics with practical examples.

What Is the CHOOSE Function in Excel?

The CHOOSE function returns a value from a list based on its position number.

Think of it like a numbered menu:

PositionOption
1Apple
2Banana
3Mango
4Orange

If you ask Excel to choose position 3, it returns Mango.

The CHOOSE function does not search a table. Instead, it uses the position number you provide to select one of its listed options.

CHOOSE Function Syntax

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

Understanding the Arguments

ArgumentMeaning
index_numThe position number of the value you want to return.
value1The first option in the list.
[value2]The second option. Additional options are optional.

Important: The position number starts from 1, not 0.

For example:

=CHOOSE(2,"Red","Blue","Green")

Excel returns:

Blue

Why? Because Blue is the second option in the list.

If the index number is less than 1 or greater than the number of supplied options, Excel returns a #VALUE! error.

How CHOOSE Works

Suppose the formula is:

=CHOOSE(3,"North","South","East","West")

Excel evaluates the formula like this:

PositionValue
1North
2South
3East
4West

The index number is 3, so Excel returns East.

The basic rule is:

Position number → Corresponding option → Returned result

Practical Examples of CHOOSE Function in Excel

Question 1: Select a Product Using a Position Number

Problem Statement

A store has three products. The user enters a position number and wants Excel to return the corresponding product.

Data

CellValue
A21
B2Laptop
C2Keyboard
D2Mouse

Excel Formula

=CHOOSE(A2,B2,C2,D2)

Expected Result

Laptop

Explanation

The value in A2 is 1. Excel returns the first option, which is Laptop.

If A2 contains 2, the result will be Keyboard. If A2 contains 3, the result will be Mouse.

Concept Covered

The index_num can be stored in a cell, making the result change when the position number changes.

Question 2: Return a Department Name

Problem Statement

A company assigns department numbers to employees. Use CHOOSE to return the department name based on the department number.

Data

EmployeeDepartment Number
Amit1
Priya2
Rahul3
Neha4

Department options:

PositionDepartment
1HR
2IT
3Sales
4Finance

Excel Formula

For Amit, whose department number is in B2:

=CHOOSE(B2,"HR","IT","Sales","Finance")

Expected Result

HR

Explanation

Amit’s department number is 1, so Excel returns the first option, HR.

For Priya, the department number is 2, so the result would be IT.

Concept Covered

CHOOSE can convert numeric codes into meaningful labels.

Question 3: Display a Month Name

Problem Statement

A report contains a month number. Return the corresponding month name.

Data

Month Number
1
4
7
12

Excel Formula

If the month number is in A2:

=CHOOSE(A2,"January","February","March","April","May","June","July","August","September","October","November","December")

Expected Results

Month NumberResult
1January
4April
7July
12December

Explanation

Excel uses the month number as the position number. For example, 4 selects the fourth option, April.

Concept Covered

CHOOSE can return a month name when the input is a month number from 1 to 12.

Question 4: Assign a Performance Rating

Problem Statement

A company uses rating codes to describe employee performance.

Rating CodeDescription
1Needs Improvement
2Satisfactory
3Good
4Excellent

Return the performance description based on the rating code.

Excel Formula

If the rating code is in A2:

=CHOOSE(A2,"Needs Improvement","Satisfactory","Good","Excellent")

Expected Result

If A2 contains 3:

Good

Explanation

The rating code 3 tells Excel to return the third option, Good.

Concept Covered

CHOOSE can map a numeric rating to a text description.

Question 5: Select a Sales Region

Problem Statement

A sales report uses region codes. Return the region name based on the code.

Region CodeRegion
1North
2South
3East
4West

Excel Formula

If the region code is in A2:

=CHOOSE(A2,"North","South","East","West")

Expected Result

If A2 contains 4:

West

Explanation

The code 4 corresponds to the fourth option, West.

Concept Covered

CHOOSE is useful for converting fixed codes into region names.

Question 6: Select a Discount Rate

Problem Statement

A store uses discount levels based on a discount code.

Discount CodeDiscount
15%
210%
315%
420%

Return the discount percentage based on the code.

Excel Formula

If the discount code is in A2:

=CHOOSE(A2,5%,10%,15%,20%)

Expected Result

If A2 contains 3:

15%

Explanation

The position number is 3, so Excel returns the third value, 15%.

Concept Covered

CHOOSE can return numeric values and percentages, not just text.

Question 7: Select a Quarterly Target

Problem Statement

A company has different sales targets for each quarter. Return the target based on the quarter number.

Quarter NumberTarget
1100000
2125000
3150000
4175000

Excel Formula

If the quarter number is in A2:

=CHOOSE(A2,100000,125000,150000,175000)

Expected Result

If A2 contains 4:

175000

Explanation

The number 4 selects the fourth target from the list, which is 175000.

Concept Covered

CHOOSE can return different numeric targets based on a position number.

Student Practice Tasks

Try these tasks yourself using the CHOOSE function.

  1. Return a color based on a code: 1 = Red, 2 = Blue, 3 = Green.
  2. Return a weekday name using a number from 1 to 7.
  3. Return a product category based on a category code.
  4. Return a customer type: 1 = Regular, 2 = Premium, 3 = VIP.
  5. Return a delivery option: 1 = Standard, 2 = Express, 3 = Same Day.

Final Student Challenge: Create a Sales Priority Label

A company uses the following priority codes:

Priority CodePriority
1Low
2Medium
3High
4Urgent

Your task: If the priority code is stored in cell A2, write a CHOOSE formula that returns the correct priority label.

For example, if A2 contains 3, the expected result is High.

Challenge Formula

=CHOOSE(A2,"Low","Medium","High","Urgent")

Key Takeaways

  • CHOOSE returns a value based on its position in a list.
  • The first option is position 1.
  • The index_num argument determines which option Excel returns.
  • CHOOSE can return text, numbers, percentages, and cell references.
  • If the position number is invalid, CHOOSE returns #VALUE!.
  • CHOOSE is suitable for a fixed list of options.

Frequently Asked Questions

1. What is the CHOOSE function in Excel?

The CHOOSE function returns a value from a list based on a position number.

2. What is the syntax of CHOOSE?

The syntax is:

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

3. Does the CHOOSE function start counting from 0 or 1?

It starts counting from 1. The first option is position 1.

4. Can CHOOSE return text?

Yes. CHOOSE can return text values such as department names, product names, and labels.

5. Can CHOOSE return numbers?

Yes. It can return numbers, percentages, and other numeric values.

6. What happens if the index number is 0?

Excel returns a #VALUE! error because the position number must be at least 1.

7. What happens if the index number is greater than the number of options?

Excel returns a #VALUE! error because the requested position does not exist in the list.

8. Can the index number be a cell reference?

Yes. For example, =CHOOSE(A2,"Red","Blue","Green") uses the value in A2 as the position number.

9. Can CHOOSE work with more than three options?

Yes. CHOOSE supports up to 254 value arguments.

10. Is CHOOSE a lookup function?

CHOOSE is often used for lookup-like tasks, but it does not search a table. It selects an option based on its position number.