How To Use The Choose Function

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 .

Transcript#

Transcript