How to Use Excel’s CHOOSE Function to Select Data Based on Criteria
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:
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:
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:
Based on these criteria, here is the formula you’re able to use:
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 alsouse it with the SUMIF statementto 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 it’s possible to 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.
Using multiple columns in Excel will save you the time of manually merging them. Here’s how to combine columns in Excel.
I found my TV was always listening—so I shut it down.
Every squeak is your PC’s way of crying for help.
When your rival has to bail out your assistant.
These plugins will make you wonder why you used Photoshop in the first place.
My foolproof plan is to use Windows 10 until 2030, with the latest security updates.
