How to Use the CHOOSE Function in Excel?

The CHOOSE function in Excel is a powerful tool that returns a value from a list based on a given position or index number. It can simplify complex decision-making processes, categorize data, or even dynamically assign values based on certain conditions.

In this blog, we will learn how to use the CHOOSE function by assigning names to employee IDs. We will explore different use cases and examples to give you a comprehensive understanding of the CHOOSE function.

 

What is the CHOOSE Function?

The CHOOSE function allows you to pick a value from a list of options based on an index number. Here’s the basic syntax:

Syntax:

=CHOOSE(index_num, value1, value2, value3, ...)

  • index_num: This is the position or number that determines which value will be selected.
  • value1, value2, value3, ...: These are the options (values) from which the function will select based on the index_num.

 

Example 1: Assigning Names Based on Employee IDs

Let’s take a simple example where you have employee IDs, and you want to assign names based on these IDs.

Employee ID

Name

1

Ajay

2

Ravi

3

Priya

4

Neha

5

Manish

 

  

Step 1: List of Employee Names

We’ll use the following names:

  • Ajay
  • Ravi
  • Priya
  • Neha
  • Manish 


Step 2: Applying the CHOOSE Function

In cell B2 (assuming column A contains employee IDs), you can write the following formula:

 

=CHOOSE(A2, "Ajay", "Ravi", "Priya", "Neha", "Manish")

Explanation:

  • If the value in cell A2 is 1, the formula will return "Ajay".
  • If the value is 2, it will return "Ravi", and so on.

 

Step 3: Dragging the Formula

Once you’ve entered the formula in B2, drag it down to apply it to all rows. Here’s what the result looks like:

Employee ID

Name

1

Ajay

2

Ravi

3

Priya

4

Neha

5

Manish

 


 Example 2: Assigning Cities to Employees

Let’s take another practical example where we want to assign city to each employee based on their ID.

Cities List:

  • Mumbai
  • Delhi
  • Bangalore
  • Kolkata
  • Hyderabad

 

Employee ID

City

1

Mumbai

2

Delhi

3

Bangalore

4

Kolkata

5

Hyderabad

 

Formula:

You can use the CHOOSE function to assign cities like this:

=CHOOSE(A2, "Mumbai", "Delhi", "Bangalore", "Kolkata", "Hyderabad")

 

Result:

Employee ID

Assigned City

1

Mumbai

2

Delhi

3

Bangalore

4

Kolkata

5

Hyderabad

This is a straightforward way to dynamically assign values from a list.

 

Example 3: Using CHOOSE with Numbers

The CHOOSE function is not limited to text values like names or cities; it can be used with numbers too.

Let’s say you have a list of employee IDs, and you want to assign salary levels (in lakhs) based on their IDs.

 

Employee ID

Salary (Lakhs)

1

6

2

8

3

5

4

7

5

9

Formula:

=CHOOSE(A2, 6, 8, 5, 7, 9)

 

Result:

Employee ID

Salary (Lakhs)

1

6

2

8

3

5

4

7

5

9

 The CHOOSE function automatically picks the salary based on the Employee ID.

 


Example 4: Combining CHOOSE with Other Excel Functions

You can combine the CHOOSE function with other Excel functions like MATCH, VLOOKUP, or IF to create more complex scenarios.

 

Using CHOOSE with MATCH for Dynamic Selection

Suppose we have a dynamic situation where we want to pick a name based on a condition. We’ll use MATCH to first find the position and then CHOOSE to return the correct name.

Here’s an example formula:

=CHOOSE(MATCH(A2, {1,2,3,4,5}, 0), "Ajay", "Ravi", "Priya", "Neha", "Manish")

Explanation:

  • The MATCH function finds the position of the employee ID in the list {1,2,3,4,5}.
  • CHOOSE then selects the corresponding name from the list.

This is useful when you have complex data tables and want to return values dynamically based on conditions.

 

Assigning Departments with CHOOSE

Let’s say you have a list of departments and want to assign employees to departments based on their ID.

Departments:

  • HR
  • Finance
  • IT
  • Marketing
  • Operations

 

Employee ID

Department

1

HR

2

Finance

3

IT

4

Marketing

5

Operations

Formula:

=CHOOSE(A2, "HR", "Finance", "IT", "Marketing", "Operations")

 

Result:

Employee ID

Assigned Department

1

HR

2

Finance

3

IT

4

Marketing

5

Operations

This allows for easy allocation of employees to their respective departments.

 

Final Remarks

The CHOOSE function in Excel is an incredibly versatile tool that allows you to select values based on index numbers, making it ideal for scenarios like assigning names, cities, departments, or even numbers. In this blog, we’ve seen how to:

  • Assign names to employees.
  • Use CHOOSE to assign cities, salaries, and departments.
  • Combine CHOOSE with other functions like MATCH.

With these examples, you can now easily use the CHOOSE function in your spreadsheets for various practical purposes.

 


No comments:

Post a Comment

MS Excel Logical Functions

Logical functions in Excel are powerful tools that help you make decisions based on conditions. Whether you're comparing values or testi...

Post Count

Loading...