Welcome to Excel Avon
SWITCH Formula in Excel
The SWITCH Function in MS Excel is used to compare one value with the values of another list and returns the first matched value if found. Returns the first exact match found as a result. This function also considers and allows the user to enter the optional default value if there is no match found.
Formula
=SWITCH(expression, value1, result1, [value2, result2], [default_or_value3, …])
Arguments
expression – The value or expression to match against.
value1 – This the test value or logical value comparing to the Expression.
result1 – The result that has to be returned when the expression matches the first value.
value2 – The second value against which the expression has to be compared when the first match turns out to be false. This is an optional parameter.
result2 – The result that has to be returned when the expression matches the second value.
default – [optional] The default value to use when no match is found.
SWITCH Formula Example
DOWNLOAD USED EXCEL FILE FROM HERE>>
As you can see we have created data sheet as an example and will use SWITCH Formula.
As can be seen in the excel sheet below, there are some random values whose rating has to be found. To find out Rating Find we have to give rating to a value.
As you can see in the below image rating of value 1 will be “worst” and rating of value 2 will be “bad”, rating of value 3 will be “good”, rating of value 4 will be “very good”, rating of value 5 will be “outstanding”.
Now I will apply the switch formula.
=SWITCH([@Value],1,"Worst",2,"Bad",3,"Good",4,"Very Good",5,"Outstanding")
After applying the SWITCH formula, we will press enter and we will find the rating of all the values.
So you can see in the above image that we have got the rating of value.
Like the IFS function, the SWITCH function allows you to test multiple conditions without nesting multiple IF statements in a single self-contained formula like the IFS function.
DOWNLOAD USED EXCEL FILE FROM HERE>>
So I hope you have understood this formula and for more information, you can follow us on Twitter, Instagram, LinkedIn, and YouTube as well.
You can also see well-explained video here
