Excel Choose Formula: A Comprehensive Guide

If you are looking for Excel formula to help you select a value from a list, then excel choose formula might be just what you need. In this guide, we will look at details about Excel Choose formula including its syntax and usage to its examples and variations.

What is the Excel Choose FORMULA?

Excel Choose formula is useful when you need to select a value from a list. Or multiple cells based on its index number. This function is very versatile and you can apply it many different scenarios like below.

• Selecting a month name based on its number.
• Retrieving data from a table or matrix.
• Finding a value from a list via user input.

The Choose function is part of Excel’s family of lookup and reference functions. This also includes the VLOOKUP, HLOOKUP, INDEX and MATCH functions.

Excel Choose Function Syntax:how to use choose function in excel

The syntax of the Excel Choose formula works like this below.

• index_num: This is the index number of the value you want to select from the list of values. It must be a positive integer between 1 and 254.
• value1, value2, …: These are the values to select. You can have up to 254 values. And they can be numbers, text, logical values or cell references.

Excel Choose Function Examples

Example 1: Selecting a Month Name Based on Its Number

If we suppose you have a list of month numbers in column A and you want to display the corresponding month names in column B. You can use the Choose function as follows:

In this example, if the value in cell A1 is 1, the formula will return January. If the value is 2, it will then bring February and so on.

Example 2: Retrieving Data from a Table or Matrix

In case we have a table with sales data and you want to retrieve the sales number for a specific region and product.

Here, cell A1 contains the row number of the data you want to retrieve. The range B2:D2 means region names and the ranges B3:D3, B4:D4. While B5:D5 contain the sales data for each product. The Choose formula will bring you sales amount for the selected region and product.

Example 3: Extracting a Value from a List Based on User Input

If you have a list of names in column A and you want to find the name that corresponds to a user input value in cell B1.

So in this case, if the value in cell B1 is 1, the formula will return the value in cell A1. If the value is 2, then excel choose formula will give us value in cell A2 and so on.

choose function excel Variations

The Excel Choose function has several variations for more advanced tasks.

Choose with INDEX: choose function on excel

The INDEX function can work in conjunction with the Choose function to extract data from a table or matrix. You can use Choose and INDEX functions together like below example.

Here, the Choose function gives you a data based on the value in cell A1. The INDEX function then selects the first row and third column of that range.

Choose with VLOOKUP

As all Excel users use and love it, VLOOKUP function is another useful tool when looking for a specific thing in lists.

You can apply Choose function to have a range of data as we stated for cell B1. Then, VLOOKUP will look for the value in cell A1. And it will give us value from the third column.

Tips for Using the Excel Choose Function

• The index number here in this formula must be a positive integer between 1 and 254.
• And this function can handle up to 254 values. But it is better to keep in mind that adding more values will make the formula longer and more complex.
• You must have ranges or lists for selecting values are of the same size and shape.
• Also, we need to be careful when selecting data from tables or matrices. Because Choose function can return unexpected results if the data is not nicely organized.

Conclusion: What is Excel CHOOSE function?

The Excel Choose function can bring us values from data list and work well with Vlookup. If you memorize the logic and syntax, you can save time and decrease yo manual work. If you like this article, you can read our Excel Function And Formulas: Data Analysis article to get info about more excel functions.

FAQs ON choose formula in excel

1. What is the difference between the Choose function and the INDEX function?

• The Choose function gives us a value based on its index number. But INDEX function returns a value based on its row and column coordinates in cell groups.

2. Can the Choose function select values from multiple sheets in a workbook?

• Yes, you can use it in combination with the INDIRECT function to select values from multiple sheets.

4. Can the Choose function work with non-numeric values?

• It can work with values of any type. Such as including text and logical values.

5. How to debug a Choose function returning incorrect results?

• So here, your ranges or lists from  having values should be same size and shape. And you need to double-check if index number is correct.