There are several functions in Excel that allow you to help select data based on certain criteria. VLOOKUP and INDEX/MATCH are two such functions that are widely used, but one lesser-known function that can help you with the same is the CHOOSE function.
In this guide, we will explain the CHOOSE function in detail and provide practical examples that demonstrate how to use it effectively in your spreadsheets. By the end of this guide, you will have a solid understanding of the CHOOSE function and how to use it to streamline your data analysis tasks in Excel.
What Is the CHOOSE Function in Excel?
The CHOOSE function in Excel allows you to pick a value from a given list based on an index number. It works by taking an index number as well as a list of values and returning the value that matches that index number.
Here are some of the use cases where the CHOOSE function in Excel can be useful:
- Data validation: You can combine the CHOOSE function with data validation to create dropdown menus based on given index numbers.
- Sorting and filtering: You can sort or filter the data in your spreadsheets based on certain criteria. For instance, you can filter a list of employees based on their ranking in the department.
- Conditional formatting: Based on certain criteria, you can apply conditional formatting to cells using the CHOOSE function. For instance, you can highlight certain cells in the list based on their position.
This function is quite similar to the INDEX and VLOOKUP functions in a way, since they also return values based on specific criteria. The main difference is that the CHOOSE function does not require a lookup range or a table, but a list of options as arguments.
How to Use the CHOOSE Function in Excel
To understand how this function works in Excel, let’s have a look at two examples: First, let’s see how we can use the CHOOSE function to select data based on an index number.
Let’s say you have a list of five countries (USA, Canada, Australia, Ireland, and Norway). You wish to use the CHOOSE function to select a country based on an index number entered in cell B2. For this, the formula that will go in the C2 tab will be:
=CHOOSE(B2, A2, A3, A4, A5)
This formula will give you a value based on the index number you enter in B2. So for instance, if you enter 3, it will return Australia.
Since the CHOOSE function can be nested, you can use it with an IF statement to return different results based on an index number. Here's a quick example of how you can do that.
Let's suppose you have a table of students (column A) and their exam scores (column B) in a spreadsheet. You want to assign a grade to each student based on these marks. The criteria are as follows:
- A: 90-100
- B: 80-89
- C: 70-79
- D: 60-69
- F: below 60
Based on these criteria, here is the formula you can use:
=CHOOSE(IF(B2>=90,1,IF(B2>=80,2,IF(B2>=70,3,IF(B2>=60,4,5)))), "A", "B", "C", "D", "F")
Here, the CHOOSE function will use the nested IF statement to determine the index number of grades according to the scores, and provide you with the achieved grade.
You can also use it with the SUMIF statement to sum the values based on criteria. However, since this function requires you to provide options as individual arguments, it is not as flexible as VLOOKUP or INDEX/MATCH.
Select Your Data Based on Criteria
The CHOOSE function can be a valuable addition to your list of Excel functions. It is a simple, yet powerful tool that you can use to manipulate data based on certain criteria. By following the steps and instructions mentioned in this guide, you can effectively use this function to optimize your workflow in Excel.