Excel Switch Statement with Ranges

Question

I need to populate a column (say A) based on the value in another (say B). However, B can have a range of values and I need to populate A based on where the value in B falls. I've tried the following and it just returns TRUE. Any thoughts on how I can Switch on a range of values?

=SWITCH(BT2,AND(BT2>MIN(0), BT2<MAX(1.4)),"LOW",
AND(BT2>MIN(1.5),BT2<MAX(4.4)),"LIMITED",AND(BT2>MIN(4.5), BT2<MAX(9.4)),
"MODERATE", 
AND(BT2>MIN(9.5), BT2<MAX(19.4)),"CONSIDERABLE", 
AND(BT2>MIN(19.5), BT2<MAX(25)), "HIGH", "?")

Columns Example

Thanks!

score 7 · Answer 1 · answered May 10 '22 at 14:45

Use CHOOSE/MATCH:

=CHOOSE(MATCH(BT2,{0,1.5,4.5,9.5,19.5}),"Low","Limited","Moderate","Considerable","High")

The Match returns 1,2,3,4, or 5 depending on where it falls in the {0,1.5,4.5,9.5,19.5} array. Then CHOOSE will return the text that equates with that number.

Or just put the values in another table:

Then use VLOOKUP:

=VLOOKUP(BT2,A:B,2,TRUE)

VLOOKUP does what VLOOKUP does and finds where the number fits and returns the value at that position.

score 2 · Answer 2 · answered May 11 '22 at 04:48

This one seems to me to be more naturally a task for IFS():

=IFS(AND(BT2>MIN(0), BT2<MAX(1.4)),"LOW",
     AND(BT2>MIN(1.5),BT2<MAX(4.4)),"LIMITED",
     AND(BT2>MIN(4.5), BT2<MAX(9.4)),"MODERATE",
     AND(BT2>MIN(9.5), BT2<MAX(19.4)),"CONSIDERABLE",
     AND(BT2>MIN(19.5), BT2<MAX(25)), "HIGH",
     TRUE,"?")

The reason SWITCH() is not working for you is that your test for each clause returns TRUE or FALSE, which alternatively Excel might evaluate as 1 or 0 (NOT in this case it seems, though you could coerce that to happen with slight modification, though there's no point, of course), and none of those four values are likely the value in BT2 so there is never a match and never a switch to perform so the default at the end of your formula is returned.

If you place FALSE in BT2 and watch it convert to the Boolean value (Excel centers it and changes it to FALSE), you will see your formula now has a return of "LOW" as it now matches the first switch pair.

I'm not sure why you use MIN() and MAX() in the tests. They don't seem required. If it just seemed like a good idea, you might drop them and just compare the direct values. If they do have a point... perhaps your real use is more complicated and they are needed, perhaps they perform a coercion I'm not realizing and it helps... something... or it too fits a more complicated actual use, perhaps... well, if not necessary for some definite reason, you might like to just drop them and make the comparisons directly.

score 0 · Answer 3 · answered May 10 '22 at 14:02

Unfortunately, I'm still not positive what you are looking to accomplish. I tried adding your formula targeting column B instead and all the results were ?, but if I used nested If statements I got the results below.

Hopefully, that's sending things in the right direction

Excel Switch Statement with Ranges

3 Answers3