Abstract
Transcript
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.
=VLOOKUP(C5,$H$5:$I$7,2,FALSE)
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.
=CHOOSE(C5,"Poor","OK","Good","Excellent")
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.
=CHOOSE(C5,$I$5,$I$6,$I$7,$I$8)
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.
=MONTH(B5)
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.
=CHOOSE(MONTH(B5),1,1,1,2,2,2,3,3,3,4,4,4)
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.
="Q"&CHOOSE(MONTH(B5),1,1,1,2,2,2,3,3,3,4,4,4)
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 CHOOSE function performs simple lookups and can sometimes replace more complicated formulas that use VLOOKUP, INDEX and MATCH, or nested IFs.
The next time you need to map whole numbers into specific values, don't forget about the CHOOSE function.
