Open In App

How to use CHOOSE Function in Excel

Improve
Improve
Like Article
Like
Save
Share
Report

The CHOOSE function is technically part of Excel’s lookup function and can be incredibly useful. The CHOOSE function returns a value from a list using an index. One of those Excel features, CHOOSE, may not seem helpful on its alone, but when paired with other functions, it offers a tonne of fantastic advantages. 

In its most basic form, the CHOOSE function allows you to select a value from a list by its position.

What is the CHOOSE Function in Excel

In Excel, the CHOOSE function acts like a menu where you can point to something in a list using a number you give it. Imagine it as a way to pick something special from a group of choices. For instance, suppose you have a list of fruit names in cells A1 to A3, and you want to choose one based on a number in cell B1. You can use CHOOSE like this: “=CHOOSE(B1, A1, A2, A3).” If B1 has the number 2, it will show the second fruit in the list, which is in cell A2. It’s helpful when you need to make decisions or display different things depending on a particular situation.

Excel CHOOSE function – Syntax and Basic Uses

Based on a defined location, Excel’s CHOOSE function is intended to return a value from the list.

Syntax:

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

Parameter:

  • Index_num: The value we want to choose. The number must be between 1 and 254.
  • Value1: The first value from which to choose.
  • Value2 (Optional): The second value from which to choose.

Excel CHOOSE function – things to remember!

The CHOOSE function is fairly simple, so adding it to your worksheets shouldn’t be too tough. The following factors may be to blame if the result your CHOOSE formula returns is unexpected or not the one you were seeking:

  • There are only 254 possible values to select from.
  • The #VALUE! Error is returned if index num is less than 1 or more than the number of values in the list.
  • If the index num parameter is a fraction, the lowest integer is used instead.

Let’s begin with an example.

How to Use CHOOSE Function in Excel

Let’s consider the following examples.

Example 1

To get our Value, follow the below steps

Step 1: Format your data

Now, if we want to get the value of any number (index) in E1. Let us follow the next step

Step 2: Enter the Formula

We will enter =CHOOSE(E1,B3,B4,B5,B6,B7,B8,B9) in E2 cell.

Here we said Excel should return our index (E1) from our list; B3,B4,B5,B6,B7,B8,B9.

This will return a #value! Error because there is no number provided in E1.

Let’s put 1 in E1. The CHOOSE function returns Apple because Apple is the value with index 1 on your list.

Example 2

Here we want to pick a month, and we want it to display the sum of revenue for that month picked.

 Step 1: Format your data

Now, if we want to get the sum of the month picked in H4. Let us follow the next step

Step 2: Enter SUM(CHOOSE(H3)

In H3, we would put a number 1, 2, or 3 which represents Jan, Feb, and March. Thus, We will enter =SUM(CHOOSE(H3)

Step 3: Enter “=SUM(CHOOSE(H3,B4:B9,C4:C9,D4:D9,E4:E9))”

This will be followed by the values in each month. Thus, This will be We will enter =SUM(CHOOSE(H3,B4:B9,C4:C9,D4:D9,E4:E9)) in H4 cell.

Step 4: Press Enter

Then we press ENTER on our keyboard. This will return the sum of values for 1 which is Jan.

How to Use CHOOSE function in Excel – Formula Examples

The examples below demonstrate how CHOOSE may supplement other Excel functions’ capabilities and offer different approaches to some common problems—even those that are widely regarded as impractical.

Returning different values depending on a set condition is one of Excel’s most common duties. In most circumstances, a traditional nested IF statement can be used to do this. However, using the CHOOSE feature can be a quick and simple replacement.

Return different values based on the condition

Supposing you have a column of Air Quality Index and you want to label the scores based on the following conditions

AQI Dataset

AQI Dataset

Nesting a few IF formulae inside of one another is one method for achieving this

=IF(E2>=401, “Severe”, IF(E2>=301, “Very poor”, IF(E2>=251, “Poor”, IF(E2>=101,”Moderately Polluted”, IF(E2>=51,”Satisfactory”, “Good”)))))

Another option is to select a label that fits the condition

=CHOOSE((E2>0) + (E2>=51) + (E2>=101) + (E2>=251) + (E2>=351) + (E2>=401), “Good”, “Satisfactory”, “Moderately Polluted”, “Poor”, “Very Poor”, “Severe”)

AQI Category

AQI Category

Instead of using hardcoded labels, you can use cell references to increase the formula’s flexibility, for example:
=CHOOSE((E2>0) + (E2>=51) + (E2>=101) + (E2>=251) + (E2>=351) + (E2>=401), $A$2, $A$3, $A$4, $A$5, $A$6, $A$7)

Using Cell Reference

Using Cell Reference

Excel CHOOSE Formula for Random Data

As you are surely aware, Microsoft Excel includes a unique function called RANDBETWEEN that creates random integers between the bottom and top numbers that you select. It should be nested inside the index num argument of CHOOSE so that your formula can produce practically any kind of random data.

For Example

=CHOOSE(RANDBETWEEN(1,6), “Good”, “Satisfactory”, “Moderately polluted”, “Poor”, “Very poor”, “Severe”)

CHOOSE formula to generate random data

CHOOSE a formula to generate random data

CHOOSE Function for Selecting Month

The CHOOSE Function is like a tool for picking a month form a list of months based on a date. For instance, if you have a adte in Excel and want to know which month it is, you can CHOOSE.

Let’s consider you have a list of dates in Column A, and you want to find the month for one of those dates, like the second date in cell A3.

Screenshot-(82)

Dates

You can use this formula: “=CHOOSE(MONTH(A4), “Jan”, “Feb”, “Mar”, “Apr”, “May”,”Jun”, “Jul”, “Aug”, “Sep”, “Oct”, “Nov”, “Dec”).”

When you use this formula, it checks the month of the date in cell A4 and then gives you the corresponding months name. So, if A4 contains a date in March it will return “Mar”.

Screenshot-(84)

Using CHOOSE Function for Selecting Month

CHOOSE Function With VLOOKUP

Utilizing the CHOOSE Function in conjuction with VLOOKUP Function allows for the retrieval off specific desired values.

You can refer to the link to learn about ” CHOOSE Function With VLOOKUP”.

Error in using CHOOSE Function

  • It returns “#VALUE! Error” occurs:
    • The “index_num” argument argument surpasses the available choices.
    • The “index_num” argument doesn’t align with a numeric value.
  • A “#NAME? Error” emerges when:
    • The value arguments are presented as text arguments without enclosing quotes.
    • Valid cell references are not furnished as arguments.

Conclusion

FAQs on CHOOSE Function in Excel

What is the CHOOSE Function in Excel?

CHOOSE Function is a built-in Function in Excel that allows users to Select and return a value from a list of options based on a specified index number.

How does the CHOOSE Function work?

It takes an index number and the list of values as arguments. Then it returns the value at the position indicated by the index number in the list. In the example CHOOSE(3,”NAME”,”AGE”, “WEIGHT”), it will return “WEIGHT” because it corresponds to the second position in the list.

What is the formula for the CHOOSE function?

The formula for using the CHOOSE function in Excel is as follows:

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

What are the use cases for the CHOOSE Function?

You can use CHOOSE Function when you have a list of values, and you want to select and return specific values based on their position in the list. It can be used with other Functions such as INDEX and MATCH to create dynamic formulas that return different values based on certain conditions.

Is CHOOSE function different from other lookup Functions?

The CHOOSE Function is different from other lookups functions like VLOOKUP or HLOOKUP because it directly selects a values based on the given index number from a predefined list of values.



Last Updated : 08 Nov, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads