Exceljet

Quick, clean, and to the point

This video is part of Core Formula, video training for Excel Formulas.

How to use the CHOOSE function

Tags 
Summary 
In this video, we'll look at how you can use the CHOOSE function for simple lookups. The CHOOSE function doesn't need an external table, and can sometimes replace more complicated formulas based on VLOOKUP, INDEX and MATCH, or nested IFs.
Video Transcript 

In this video, we'll look at how you can use the CHOOSE function.

Let's look at three examples.

Here have some items listed with a numeric color code. We want to bring these names into the column D.

Now, since I already have the table already 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 hardcoded 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 particual 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

So that's it.

The CHOOSE function performs simple lookups and can sometimes replace more complicated 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.

Author 
Dave Bruns

Related shortcuts

This is fantastic. I am working on a report and this is exactly what I needed. Thanks. -Diane
Excel foundational video course
Excel Pivot Table video training course
Excel conditional formatting video course
Excel formulas and functions video training course
Excel Shortcuts Video Course