These Google Sheet Tips are fantastic!!! Every week, I try to use the new tip / idea / code in one of my projects. Great stuff!
– Chip D.
Join our community of over 50,000 Google Sheets aficionados and get:
- The Google Sheets Tips email – an actionable tip every Monday (think of it as your Monday morning espresso, in spreadsheet form.)
- Bonus: A free copy of the Spice Up Your Sheet Life ebook, packed with 100 Google Sheets tips (the first 100 from this series)
- Exclusive content not found anywhere else on this site
- Free updates to any templates
- The best deals on future training courses
Click here to see the archives.
Example Google Sheets Tips email
(This was Google Sheets tip #156, originally published on 7th June 2021.)
GSheets Tip: CHOOSE your own adventure
Hi friend,
Here is your Google Sheets tip #156, which you can apply to your work immediately.
Think of it as your Tuesday morning espresso, in spreadsheet form.
TIP: Use the CHOOSE function for a quick lookup solution
I wrote this Monday tips email whilst I was following the qualifying action for the Baku Formula 1 race over the weekend.
The Baku circuit in Azerbaijan is a narrow street circuit that punishes any mistakes by the drivers. Several drivers misjudged turn 15 and crashed into the wall, ending their qualifying efforts for the day.
When the commentators reviewed these crashes, they noticed drivers locking up their front wheels as they braked, which meant they lost grip going into the corner and hit the barrier.
They had a split second to make a choice: take the corner at race speed or abandon the lap and turn right into the run-off area, saving the car for another go.
This idea of choosing between different options reminded me of the CHOOSE function in Google Sheets, so I decided this would be a good tip for today.
What is the CHOOSE function?
As the name implies, a CHOOSE formula lets you choose between different options.
It’s a lookup function, akin to a limited VLOOKUP rather than an alternative to the IF function.
You give it a number and it returns a value from that numbered position in the array.
Here’s a simple example:
=CHOOSE(1,"Turn left","Turn right")which will output:
The first argument is the input number, in this case, 1.
All the subsequent arguments are options for the output. The number is then used to retrieve the output value at that position. In this case, it’s the first value we want, hence the formula returns the value “Turn left”.
If I’d put a value 3 as the input, but had no third option for the output, then the formula returns a #NUM! error.
When to use CHOOSE
The CHOOSE function can be used to lookup values when you have a limited number of them. It’s simpler and quicker than creating a VLOOKUP but doesn’t work so well with lots of values.
Here’s an example dataset showing students and their preferred activity 1, 2, or 3.
The CHOOSE function can quickly convert that to the activity name:
=CHOOSE(B2,"Robotics class","Woodwork class","Art class")
In this example, we referenced cell B2 as the input. It just takes whatever number is in B2 as the input value.
Here’s another example dataset with a code that captures the sale referral type. CHOOSE is used to convert the code to a description that we can understand:
=CHOOSE(E3,"Direct Traffic","Email list","Affiliate","PPC")
How will you choose to use the CHOOSE function?
Have a great week,
Ben