From the course: Excel Tips Weekly

Use SWITCH, IFS, and CHOOSE functions, contrasted with XLOOKUP - Microsoft Excel Tutorial

From the course: Excel Tips Weekly

Start my 1-month free trial Buy for my team

Use SWITCH, IFS, and CHOOSE functions, contrasted with XLOOKUP

“

- [Instructor] When you're trying to look up data in Excel, sometimes you'll use a list. Sometimes you'll use it within a function. You're likely to be thinking of the XLOOKUP function or perhaps its companions, VLOOKUP and HLOOKUP, and those are powerful tools, but there are other functions as well too. And when you're trying to track these down, there might be a slight bit of confusion, at least at first. As you go to the Formulas tab in the ribbon, if you go to Lookup & Reference, you certainly will find in here, and they're alphabetical, of course, you'll see XLOOKUP at the bottom, but also a function in here called CHOOSE up top, but also a function called CHOOSE. Now the other two functions shown in the blue panel to the right are not LOOKUP functions, but yet they have a lot in common with these two functions. SWITCH and IFS are found under the category Logical. Now if you've worked with the IF function, you obviously would expect IFS to be in the same category and it is, but also the SWITCH function too. And they all have something in common in that they allow us to test for a condition and then provide an answer. And of these four, only LOOKUP requires an external table, and that's not necessarily bad. In fact, sometimes it's exactly what you want. And so in the worksheet in the background here, we've got some scores that are emerging in column F on the basis, at least initially, of XLOOKUP. So I'll double click on cell F2 here. And just a quick reminder, what we're doing here, we're looking up data in cell E2. We're looking over in column A, trying to find that. If we do find it, we go into the corresponding entry in column B. If we don't find it at all, we'll put in. in this case, a zero. Now we could put in "Not Found" if not found. We could do it that way too, if we wish. Or any other number or texts that that we might want in this situation. At the moment, it is found, but if I put in a different rating here that isn't in the list, you know, we get that, so that's pretty much self-explanatory once you see it. Now starting in row 5, I'm doing something a little bit different here, and that's pretty long and pretty lengthy, and it looks like it would be pretty tedious to set up. It's using the SWITCH function. And you can quickly see the logic here. What we're saying here is look at the data in cell E5. If it happens to be equal to the word Excellent, then we'll put in 99. If that check fails, we're going to see if it's equal to VeryGood, and if that's true, we'll put in 92, and so on and so on and so on. And when we get to the end here, we put up final comma and then whatever we want to appear, if not found, so in this case, it's zero. But here too, I could put in the phrase Not Found, but that certainly is a lot of typing to get it set up. But the big advantage, and it may not be that big, but you'll have to decide, no external table is required. Now imagine if that list in column A had 10 entries, this is really going to get tedious. What if it had 20? At some point, it becomes perhaps unthinkable, and maybe XLOOKUP is the way to go. So at different times, you might be able to think that out. And there's a third possibility here. We could be using a function called IFS, I-F-S, pretty much like SWITCH and also like the IF function, but we don't have to keep repeating, like the IF function does, the word IF. So here, just as we get started here, we're looking at cell E8. If it's equal to Excellent, we're going to put in a 99. If that doesn't work, we're going to check to see if it's equal to VeryGood, and so on and so on and so on, same general idea. At the end of this, it might be a little tricky at first. If we want to provide an answer for when that rating is not found, we put in the word TRUE, a comma, and then what it is that we do want to appear there when it's not found. So that's similar to the way the SWITCH function was using a default entry at the end there. Now we haven't seen the CHOOSE function just yet, but we've got another situation over here, making column G narrower here. Here are all the formulas, by the way, that we just saw to the left. Use that for reference later if you're looking this up. But let's say we're trying to calculate the quarters here. In this case, these are all first quarter. We see the answers over there. Second quarter, and we're using a function here not related to the others that we've seen so far, ROUNDUP. Now that's perfectly acceptable, and it might be exactly what you want, and I think it's just fine. Some people might be a little intimidated by this, and particularly if we're going to be changing our fiscal year system so that either July 1st is the beginning of the fiscal year or maybe October 1st, and there could be other options as well too. You have to finagle or work with this in a different way, but that could be acceptable. What are we seeing here, though, in row 7? The CHOOSE function. Now the CHOOSE function works only with numbers, and it works efficiently or reasonably efficiently with small numbers. What we're saying here is, let's look at the month in cell K8. Those entries can only be 1 through 12. We provide 12 different answers. If the month happens to be 1, our answer is 1. In other words, January is in the first quarter. If our answer is 2, here's our second entry over here, happens to be the second one, but that's in February. That's the first quarter. Same thing, this is for March. If it's April, then that's in the second quarter. It's the fourth entry that we see here. You get the idea. And if there's an advantage here, well, like ROUNDUP function, we did need an external table. But the other advantage would be what if we change or what if it's a different fiscal year system, and it begins in July? All we need to do here is take these entries right here, so I'll press Control + X to get rid of them and put 'em at the end, and invariably, I have to put in an additional comma or get rid of a trailing comma. So if your fiscal year begins in July, we're going to see a 1 here. But what's happening here? The month here is going to be 7. What's our seventh entry in this list? 1, 2, 3, 4, 5, 6, 7. It happens to be a 1. So you could easily adjust this, and if it began in October, a similar adjustment there too. But you can imagine if we need multiple answers or many, many answers, this isn't going to work so smoothly. But it certainly has its merits, and although it isn't really documentation, it's perhaps easier to understand than using a function like ROUNDUP. So we've seen some different examples of these functions. Going back here to the blue panel, the XLOOKUP function, which does require an external table, and is handy particularly as the table gets larger and larger and larger. It might be the only one to consider using, but also at different times, the CHOOSE function, the SWITCH function, and the IFS function.

Contents