Isnt Choose easier for this eg: CHOOSE(LEN(B2),"Pass","Eh…","Not bad","Get your ticket!","Must See") or CHOOSE(d2,0.2,0.15,0.10,0.07) - this is a bit shorter as you dont have to repeat the value. The only thing is you dont have an "anything else" result, but to be honest it might be better to error that anyway, or you could wrap the choose in IFERROR
Easier? Maybe. It depends on the situation. In my example, CHOOSE would work well and be less lengthy. SWITCH is just another option for when CHOOSE can't do the same alone. You're right, that you could wrap CHOOSE in an IFERROR as a catch-all. But CHOOSE also relies on an index number to determine the return. So if I didn't end up using a five-star rating, or the employee tier, and instead used some kind of text alternative, then you'd have to include something like a MATCH function for the returns. At that point, I'd probably prefer to go with SWITCH. But great point!
In a way, yes. But it is very limited! The values/result in your array would have to match exactly to the expression to return the results in a single column. However, you can get around it with other functions, but Switch isn't necessarily built for it. In most cases where I've used it, Switch only correctly evaluates the first element of the array. I could be wrong, but I can't think of a way to make it work without using some combination of alternative functions.
Thank you for this bcje video 📹 👍
Isnt Choose easier for this eg: CHOOSE(LEN(B2),"Pass","Eh…","Not bad","Get your ticket!","Must See") or CHOOSE(d2,0.2,0.15,0.10,0.07) - this is a bit shorter as you dont have to repeat the value. The only thing is you dont have an "anything else" result, but to be honest it might be better to error that anyway, or you could wrap the choose in IFERROR
Easier? Maybe. It depends on the situation. In my example, CHOOSE would work well and be less lengthy. SWITCH is just another option for when CHOOSE can't do the same alone. You're right, that you could wrap CHOOSE in an IFERROR as a catch-all. But CHOOSE also relies on an index number to determine the return. So if I didn't end up using a five-star rating, or the employee tier, and instead used some kind of text alternative, then you'd have to include something like a MATCH function for the returns. At that point, I'd probably prefer to go with SWITCH. But great point!
Will the values accept an array? IE, if you have an expression to scrutinize, can you then put the test values into an array and call the array?
In a way, yes. But it is very limited! The values/result in your array would have to match exactly to the expression to return the results in a single column. However, you can get around it with other functions, but Switch isn't necessarily built for it. In most cases where I've used it, Switch only correctly evaluates the first element of the array. I could be wrong, but I can't think of a way to make it work without using some combination of alternative functions.