CHOOSE

The CHOOSE function allows you to select a value from a list of values by specifying an index number. It’s particularly useful when you have a predefined list of options and need to dynamically select one based on a condition or input.

Consider a simple scenario where you have three fruits, and you want to choose one based on an index number. Formula like this:

Will return Banana as the result. You can make the CHOOSE function more dynamic by using a cell reference for the index number, allowing the value to change based on user input.

As in the example in the previous chapter, we can use named ranges. For this, we can again define a name for a Fruits range and use it in the formula.

Both the CHOOSE and INDEX+MATCH functions in Excel are used for retrieving values from a list or an array, but there are some differences between them. Here is what you should know and the cases where you might want to use CHOOSE.

CHOOSE

Use the CHOOSE function when you have a small, fixed set of options and you know the index of the option you want to return.

CHOOSE is more suitable when the options are straightforward and do not require a lookup in a table or range.

And here is the cases you might want to use INDEX + MATCH combination.

INDEX + MATCH

Use the INDEX/MATCH combination when you need to perform a dynamic lookup based on changing criteria.

INDEX/MATCH is ideal for situations where you need to look up values in a table or range based on specific criteria rather than a fixed index number.