Excel is great for storing information, but it can be hard to find what you're looking for when datasets get particularly large. The CHOOSE function is a fantastic workaround to that problem, helping you find the information you need quickly and easily. Here's how to use the CHOOSE function in Excel.
The instructions in this article apply to Excel 365, Excel 2019, Excel 2016, and Excel online.
What is the CHOOSE Function in Excel?
The CHOOSE function outputs a value from a list when you give it a specific position or index to draw from. If you created a numbered list, for example, and gave it a number to draw from, then it will return the corresponding value at that numbered point on the list.
In practical terms, this can be useful to find the name of something using an identifying number, like a person on an electoral register, or a product from a catalog.
How to Use the Choose Function in Excel
The CHOOSE function is a relatively simple one to use in Excel, though it can take a little longer to set up, depending on the size of your dataset.
Learn How to Use the AND, OR, and IF Functions in ExcelIn our set example for how to use the CHOOSE function, we're keeping things simple with a limited dataset of eight products in a stationary catalog. We want to find out what a product is using its single digit product number. Your dataset may vary, but you can follow along and create a comparable dataset to practice with.
-
Open your Excel document or create a new one and import your chosen data set. Make sure the list of items is numbered in a similar manner to our example.
-
Select a cell where you want your CHOOSE output to appear. You'll then type in the CHOOSE function, which, when complete, will be written in the following format:
=CHOOSE (index_num), value1, value2, [...])
-
Type =CHOOSE and double-click the CHOOSE function that appears. Alternatively, use the function menu to select CHOOSE and input your numbers and values that way.
-
Select the cell where you want the CHOOSE input to come from. In our case, that's the cell under the second Number heading, F6.
-
Type a comma, then select the first value for your list. In our case, that's Pencil, cell C6.
-
Type another comma, then select the second value for your list. Continue doing this until you have selected all values and end your function with a closed bracket. Our eventual function reads like:
=CHOOSE(F6,C6,C7,C8,C9,C10,C11,C12,C13)
The Excel CHOOSE Function Output
Don't fret that the CHOOSE cell now displays a #VALUE! error. That's simply because it doesn't have a number to draw from. To make full use of your CHOOSE function, type a number that corresponds to your values in the index number field we specified in the CHOOSE function. It should then change that error into the corresponding value.
In our case, typing 1 outputs Pencil. Typing 5 outputs Pens, and so on.
This is a very basic example of how to use the CHOOSE function, but you can specify ranges instead of individual cells for values, if you so choose. That means you can have multiple outputs for each index number, letting you find a lot of information very quickly. It can even be combined with SUM and other functions for additional functionality.