In this video we'll look at how you can use the CHOOSE function.
Let's look at three examples.
In this first example we have some items listed with a numeric color code. We want to bring these names into column D.
Now, since I already have a table here, I could just use VLOOKUP and reference the table. I get the lookup_value from column C; the table is the range H5:I7, locked with F4; the column is 2, and I need to use FALSE to force an exact match.
When I copy the formula down, we have our color names.
This works fine, but we can do the same thing with CHOOSE without a table.
With CHOOSE, the first argument is an index, and the remaining arguments are choices. If I use 2 for index, and provide "red", "green", and "blue" as values, CHOOSE returns "green", since green is the second item.
=CHOOSE(2,"red", "green", "blue")
Since we already have numeric codes in column C, I can just replace the hard-coded index with a cell reference and copy the formula down.
=CHOOSE(C5,"red", "green", "blue")
We get the same result as VLOOKUP, and we don't need this table at all.
In the next example we're translating a 4-point rating scale to text values, where 1 is poor, and 4 is excellent.
In column D, we have a classic nested IF formula.
I can replace this formula with a simpler formula based on CHOOSE.
When I copy the formula down, we get the same results.
CHOOSE also works with cell references. If I want, I can pick up values from the table directly and use them inside CHOOSE.
Now the table works like a dynamic key. If I change a value in the table, it's reflected in the formula.
In this last example, we'll use CHOOSE to assign a quarter to any date.
First I'll use the MONTH function to extract a number between 1 and 12 from each date.
Next, I'll simply wrap the MONTH function inside CHOOSE and use MONTH to generate the index. I then need to provide 12 values, one for each month.
The result is a number that corresponds to a quarter. As a final touch, I can use concatenation to add a "Q" before the number.
What's cool about this particular solution is the order of the choices can easily be changed to accommodate fiscal quarters that have different start months.
=CHOOSE(MONTH(B5),1,1,1,2,2,2,3,3,3,4,4,4) // Jan start =CHOOSE(MONTH(B5),4,4,4,1,1,1,2,2,2,3,3,3) // Apr start =CHOOSE(MONTH(B5),3,3,3,4,4,4,1,1,1,2,2,2) // Jul start =CHOOSE(MONTH(B5),2,2,2,3,3,3,4,4,4,1,1,1) // Oct start
And that's it.
The next time you need to map whole numbers into specific values, don't forget about the CHOOSE function.