CHOOSE - A Lookup Function

CHOOSE FUNCTION

I am writing a new CPE EBook on advanced functions and starting with some of the lookup functions. I took a look at the CHOOSE function as I have always been a bit curious about it. You can do a couple of different things with it but today I am just going to talk about how to use it as a basic lookup function.

The CHOOSE function is in the lookup and reference category. The syntax is =CHOOSE(Index_number, Value1, Value2,…Value254).It returns the values from an existing list.
So- what does that mean?

Excel looks at the index number and then searches for a match in the list of values. 
It works pretty simply. =CHOOSE(3, 10,15,20,25,30) = 20.

Since the Index Number is 3, CHOOSE selects the 3^rd value in the list which is 20.

See, I told you it was simple.

Let’s go through a basic example. I have a list of employees and want to determine their tax rate based upon their filing status. For informational purposes, I have a table over in Column G and H showing the filing statuses and the corresponding tax rate. 

In the example above, I want to look up and assign a tax rate based upon the filing status shown in Column D. Instead of using a lookup table as we would with VLOOKUP, in the CHOOSE function, all the information is included in the function arguments.

The equation we would use is =CHOOSE(D4,15%,25%,28%,33%,35%).
We are telling Excel to look at D4 and then find that corresponding number in the list. Cell D4 has a filing status of 4 so Excel goes to the 4^th item in the list which is 33%  and returns it. If we copy the formula down,  Excel would show that Madison has a filing status of 3 and so would return the 3rd value in the list which 28%.

The big difference between CHOOSE and the VLOOKUP is that all the data is housed inside the function arguments rather than referencing a lookup table. If you have a lot of things you are looking for then CHOOSE could be cumbersome especially if you need to edit and update it frequently.
The advantage is that you don't need to worry about sort order, once the data is entered, or absolute cell references.