Use CHOOSE to calculate fiscal quarters and as a VLOOKUP alternative

- [Instructor] Excel's Choose function has a number of different applications, and although sometimes it might be unwieldy or a bit longer, sometimes it does provide additional documentation. On this worksheet here we've got some data entries in column A and we're trying to calculate the Fiscal Year Quarter for each of these. And to this organization, based on the list we see to the right, the Fiscal Quarter matches up with the chronological quarter, so first quarter, January, February, March, second quarter, April, May, June, and so on. And here's the formula is cell B2.

If you're a mathematician, or if you're pretty adept with using Excel functions you might write the formula this way. And there's certainly nothing wrong with it, and most of all, of course, it does get the answers correct. By the way, the month function, if you've never seen it before, simply will extract the month from a date entry, and so, this answer right here can only be the numbers one through 12. So in the case here, it's 10, and what do we do here? We add two, that makes it 12, we divide by three. The INT function, meaning integer, simply ignores any decimal portion that might be there.

And the INT function, standing for integer, simply throws away any decimal result that might occur here and keeps the whole number. Important thing is it's correct. Do others understand it? Well, not necessarily, but do they care? Probably not, if it gets the answers correct. Now, we could use the Choose function here, it's going to be longer, then, at first, you might say, why bother? But it does have a bit more flexibility and it does provide additional worksheet documentation, which in some environments is more important. Equal, choose, we'll also see in a later example, by the way, we can use this as somewhat of an alternative for certain kinds of VLOOKUPs.

The Choose function begins with a simple idea. We've got some value somewhere that's been calculated, or we're pulling it out of a cell, something like that. In this case, it's going to be the month that we extract from that entry over in cell A2. We can only get the answers one through 12 here. Now we're going to provide 12 answers. So if this is equal to a one we're going to provide an answer, if it's equal to a two we're going to provide an answer, and so on, and so on, and so on. So whatever you choose here either it has to be a pretty low number or you've got some kind of a calculation that turns it into a fairly low number.

And that doesn't make a lot of sense just now, let's say, but with this example and the subsequent example it's going to make a bit more sense. So think of this an example here as only being a one through 12. Now, if it's January, February, or March our answer will be one, or two, or three, as we use this. And all those are in the first quarter, so here are the answers that we will have here if month A2 is equal to one, two, or three. And if it's four that means that it's April, so that's in the second quarter, May is the second quarter, and so on, I think you get the idea here.

We'll have three threes, and then three fours. And I'll complete the answer with Control + Enter, so the actual cell doesn't move down. Double-click from the lower right-hand corner. And we have the same answers that we see on the column to the left. And by the way, to expose both of these together we can press Control + ~, that's a quick keystroke shortcut, don't use the Shift keys, you do this and it'll make column C a bit wider momentarily. You can compare the two and, of course, the entry in column B is a lot shorter. That doesn't always mean it's better but someone looking at this might begin to quickly understand what the Choose function is doing here.

And it does have the advantage here of being more flexible if we change our quarter system. I'm going to press Control + ~ again, and by the way, that's a shortcut I think a lot of you know, for a feature found on the formulas tab up on the ribbon, right here, Show Formulas. So, what if this company changes its quarterly system and they say, okay, like in the federal government we want our fiscal year to begin in October, so for reference over here I'm simply going to take these three months and drag this leftward with the Shift key held down, drag it over to here, and simply move them over.

So now our first quarter begins October, November, December, second quarter, January, February, March, and so on. So what do we do here? We want our first three answers, in other words, when the month is January, February, or March, we want those to be in the second quarter, so we'll simply take these three numbers right here and cut them, or press Control + x, and we'll put them at the end, Control + v. Enter, we're all set, that's much easier to adjust than the formulas that we've seen over in column B.

I'll copy this down the column by double-clicking, and we've got a bunch of correct answers here for our new quarterly system where the fiscal year begins in October. So the interesting use of the Choose function. Remember, there are some severe limitations. The numbers we're working with here have to be somewhat low numbers, in other words, we have to provide an answer for when this is a one, that the answer. When it's a two, and so on. So if you do have larger numbers you're going to have to divide them or do something with them to convert them into lower values so you can work with the Choose function.

Here's another example in a different worksheet here. In column A we've got Job Ratings, only one through five. But what we want to have associated with them is a verbal score, a text score, three is Good. And you see that list over in columns F and G. And what's going on in column B, right here, is actually a VLOOKUP. Now, you don't necessarily have to know too much about a VLOOKUP, but what's happening here is the VLOOKUP function is taking that value from column A, comparing it with the entries in column F over to the right, and whenever there's an exact match we pick up the data from the second column, so the two here means get our answers out of the second column of that table of data that's in columns F and G.

The zero here, which just as easily could've been the word False, which means, in both cases, exact match, we have to find an exact match. So nothing truly wrong with that, however, we could use a Choose function here and not need that table over there. I'll use it right here, first. Now, instead of doing a lot of typing, and you don't want to watch me type, this is what I need to put in to the Choose function, right here, put this in ahead of time, so I'll simply highlight this data right here, press Control + c, and Esc. So, I'll be using this in the Choose function, but here, too, the function here will be based on what we see in cell A2, and those answers that come out of A2 can only be the numbers from one through five.

Now I will paste in what I just copied with a Control + v. So if this answer is a one, we want Poor to appear here. If it's a two, Fair, three, Good, and so on, that's all we need to put in here. I'll complete the entry with Control + Enter, and we're going to be seeing the same answers. And we don't need those entries over in column B, so I could move the score over here, just right-click and delete column B, and we don't need that table over in columns E and F. So you could think of Choose here as being an alternative to VLOOKUP, but it doesn't need an external table.

So I think you could describe Choose here as an alternative to VLOOKUP, it's certainly longer than the VLOOKUP function was but it doesn't need that external table, and in some cases that's an advantage. So, two major uses of the Choose function here. Sometimes it's better for long-term documentation, as we saw in the previous example, and the example here, we didn't need to use an external table. So it's a valuable function for some environments.