I love using the Switch function in Access. It's a real timesaver in situations where complex nested IF functions are needed. Ever spend 10 minutes trying to get a nested IF formula right?
The Switch function allows for a easier method, evaluating a list of test expressions and returning the result for the first test that evaluates to true.
The basic syntax for the Switch function is: Switch2(Test1, Result1, Test2, Result2, etc...)
Here's an example.
Switch([Expression]< 10, "Low",[Expression] > 15, "High", True, "Middle")
In this formula, if the value in the given expression is less than 10 then "Low" is returned. If the expression is greater than 15 then "High" is returned. If none of the tests evaluate to true, then "Middle is returned.
In Excel, you can't use the Switch function in a spreadsheet environment; it doesn't exist as a Worksheet function.
So I put together a small function that allows me to leverage this cool function directly in my spreadsheets.
-
'Function: The function will return the result for the first test that evaluates to true
-
'Usage: Enter this function into your spreadsheet with the required arguements
-
'Basic Syntax: Switch2(Test1, Result1, Test2, Result2, etc...)
-
'Rules: You must have at least one test/result pair.
-
'You'll have to add to the code if you want more than 14 tests.
-
'Example:'=Switch2(C4="Apple","Red",C4="Grape","Purple",C4="Orange","Orange", TRUE, "No Color Match")
-
'Note the use of the test/result combination "True, "No Color Match" so that the function will return
-
'something if none of the tests evaluate to true
-
-
Function Switch2(Test1 As String, Result1 As String, _
-
Optional Test2 As String, Optional Result2 As String, _
-
Optional Test3 As String, Optional Result3 As String, _
-
Optional Test4 As String, Optional Result4 As String, _
-
Optional Test5 As String, Optional Result5 As String, _
-
Optional Test6 As String, Optional Result6 As String, _
-
Optional Test7 As String, Optional Result7 As String, _
-
Optional Test8 As String, Optional Result8 As String, _
-
Optional Test9 As String, Optional Result9 As String, _
-
Optional Test10 As String, Optional Result10 As String, _
-
Optional Test11 As String, Optional Result11 As String, _
-
Optional Test12 As String, Optional Result12 As String, _
-
Optional Test13 As String, Optional Result13 As String, _
-
Optional Test14 As String, Optional Result14 As String)
-
-
Switch2 = Switch(Test1, Result1, _
-
Test2, Result2, _
-
Test3, Result3, _
-
Test4, Result4, _
-
Test5, Result5, _
-
Test6, Result6, _
-
Test7, Result7, _
-
Test8, Result8, _
-
Test9, Result9, _
-
Test10, Result10, _
-
Test11, Result11, _
-
Test12, Result12, _
-
Test13, Result13, _
-
Test14, Result14)
-
End Function
Simply copy the code into a standard module and call up the function using Switch2.
Why use Switch2?
For me, the Switch function is ideal when I want to avoid using nested IFs. While there is nothing wrong with nesting IF functions, there are situations where the numbers of conditions that need to be evaluated make building a nested IF impractical.
To illustrate this point, consider this example.
It is common practice to classify customers into groups based on revenue. Imagine that your organization has a policy of classifying customers into four groups: A, B, C, and D.
| Annual revenue | Customer Classification |
| >= $10,000 | A |
| >=5,000 but < $10,000 | B |
| >=$1,000 but < $5,000 | C |
| <$1,000 | D |
With the IF function, this situation calls for some hefty nesting. That is, you will have to use IF expressions within other IF expressions to handle the layers of possible conditions.
As you can see, this can get convoluted. And the chances of making a syntax or logic error are high.
You could simplify the formula by using this, but there are still enough syntax pitfalls to make you swear like a sailor.
In contrast, the Switch2 function is relatively straightforward:
=Switch2(A1<1000,"D",A1<5000,"C",A1<10000,"B",TRUE,"A")
This workaround isn't for everyone. But for those of you who are up to your necks in nested IF formulas, it can help simplify life.
What about speed difference between IF and SWITCH2 for 10000 rows for example?
Noone: I tested 1,046576 rows. In terms of speed, nested IFs are about 2.5 times faster than the Switch2 function.
If you're looking for speed over convenience then Switch2 is not for you.
Doesn't LOOKUP do the job just as well?
=LOOKUP(A1,{0,1000,5000,10000},{"D","C","B","A"})
Bob: You're too smart for me! I suppose it does for this example. However, Lookup would work for textual evaluations?
It seems like a VLOOKUP or HLOOKUP would work just as well and they would have the advantage that you can update your reference table easily as well. VLOOKUP, HLOOKUP, and LOOKUP also work for textual references.
I don't usually use the LOOKUP function so I can't speak to the advantages or disadvantages of "LOOKUP" vs VLOOKUP and HLOOKUP.
I did a quick test of some random text - the words in one paragraph above and it works, as long as the compare values are in ascending order.
=LOOKUP(P12,{"a","g","t","to"},{"beginning","middle","high","end"})
There is also the CHOOSE function that is interesting.
=CHOOSE(Index_Num, value1, value2,...)
In this case, just sorting the customer list by dollar volume should suffice.
A SWITCH function like Access would be useful in Excel, but in its absence, Excel provides a plethora of lookup functions, as many folks have mentioned. These lookup functions may explain why SWITCH was never implemented in Excel, or the absence of lookup functions may explain the existence of Switch in Access!
For inexact matches such as given in the example, LOOKUP is the simplest function. In fact, it's commonly used for determining tax rates, simple commission rates and so on. For text, you usually want an exact match, so LOOKUP isn't the best function. In this case, the "switch" values would be placed in two columns and VLOOKUP used.
The Switch2 function essentially re-invents Excel lookup functions with no obvious additional benefits.
How would we tweak the function to return the result as numeric? (sorry, my VBA is clearly lacking)
Thanks!
The Switch function you are using in Access is simply the VBA Switch function. Why not write Switch2 as a wrapper for the VBA Switch?
I haven't checked, but you may need some code to convert the original ParamArray into something that can be passed into Switch - maybe call it using Eval?
Hi Mike. The Switch function might not exist as a Worksheet function, but you can use it in MS Query. I'm doing just that: sucking some data off the spreadsheet, and using some SQL - including the SQL switch function - in MS Query to 'rearrange' things in a much better way than I could if using formulas or VBA. (in fact, this might be a future post idea for you. I can provide you with a good scenario if you like).
Originally I was trying to use the SQL CASE function, but it turns out you can't use that function when using MS Query to query Access or Excel (although it works fine if you are querying SQL Server). Had me scratching my head for a while.
Jeff: Great idea! I had never thought of that.
If you want to do a write up on it with screenshots, send that in as a guest post.
Or I can just steal your idea and post it myself.
I would never have thought of it either, had a particularly messy challenge no presented itself. Necessity is the mother of all battles, as the old saying goes.
It would be great to write this up as a quest post, as I suspect I’ll learn heaps more from both the writeup and any comments on the post. Stay tuned...but don’t hold your bacon (as another old saying goes).
I would prefer a vlookup function over here
We can create a list like below in separate column D , E and F.- where D is the start range , E is end range and F is the result.
D E F
1 10 Bad
11 20 Good
21 30 Excellent
I require result for value 23 which is entered in cell A1 so the formula in B1 to be entered is .
=VLOOKUP(A1,D:F,3)
which will display Excellent.
same way you can have multiple ranges.....
hope this will be helpful and value addition.
take care ... have a great day.
thank's! I love switch2 function. I agree that "lookup" is useful but anyway it's a good point having such a function.
see you!
I have to say this function is a lifesaver for me. Many thanks for developing it!
I am currently working on a budget model having about 300k rows and this function is just ideal: lightweight, easy to type-in and it works!
LookUps would take ages to go through 300k rows whereas this function does the job instantly even if you use nested Switch2
Brilliant, much appreciated
LOOKUP isn't an alternative to SWITCH2! Lookups can compare values but not evaluate expressions.
Well done DataPig, I've added this one to my global functions and I've used it already!
This is the first time I have seen this blog article and just wanted to mention a possible "gotcha" involved with using your Switch2 function. Since it is using VBA's Switch function underneath to perform it "magic", it also inherits VBA's Switch function's weakness as well. That weakness is the fact that VBA's Switch function does not do "short circuit" evaluations. In other words, the VBA Switch function evaluates each of its arguments (Tests and Results) even if an early Test argument's logical expression evaluates as True so that an error in a later argument cause the whole function to error out. Here are two examples to show the problem. Put these values on your worksheet.. A1=1, A2=2, A3 = 0, B1=1, B2=2, B3=0 and then try these two Switch2 formulas...
=Switch2(A1/A2=0.5,"YES",A2/A3=1,"NO")
=Switch2(A1+A2=3,A1/A2,A2+A3=5,A2/A3)
Even though the first TEST argument is True in both cases, the two functions return a #VALUE! error because of an evaluation problem in a later argument. For the first Switch2 formula, the 0 in A3 makes the second TEST argument has a "division by zero" problem which brings it down even though the first TEST argument evaluates to True; whereas the second RESULT's "division by zero" error brings down the second formula even though, again, its first TEST argument evaluates to True. Change the 0 in A3 to a non-zero number and the first formula's error goes away... same is true for the second formula if you replace the 0 in B3 with a non-zero value.
I have not tested it extensively (hardly at all if truth be told), but here is a new Switch2 function which, besides being much shorter than you function, seems to avoid the problems I mentioned in my previous posting. On top of that, I believe it will execute much quicker as well.
Function Switch2(ParamArray TestsAndResults())
Dim X As Long
For X = LBound(TestsAndResults) To UBound(TestsAndResults) Step 2
If TestsAndResults(X) Then
Switch2 = TestsAndResults(X + 1)
Exit For
End If
Next
End Function
Just bumping this into view in the Recent Comments for datapig to see in case he missed my two comments to this blog article during his "absence".