SWITCH

SWITCH(expression, value1, result1 [,value2, result2] [,default])

Returns the value based on a list of exact matches.

expressionThe expression to be compared to the values.
value1The value to compare.
result1The result if the expression equals the value.
value2(Optional) The value to compare.
result2(Optional) The result if the expression equals the value.
default(Optional) The value to return when there are no matches.

REMARKS
* Added in Excel 2019 to allow multiple conditions based on a list of exact matches.
* For an overview of conditional formulas refer to this page
* This function is not case sensitive when matching text strings.
* This function does not support wildcards (? and *).
* This function will return the first matching item in the list.
* The "values" can be numbers or text.
* The "results" can be any expression.
* If none of the "values" match and a "default" value has been provided, then the default value is returned.
* If none of the "values" match and a "default" value has not been provided, then #N/A! is returned.
* You can have a maximum of 126 (values and results).
* If the function is not available, then #NAME? is returned.
* You can use the IF function to return the value based on whether a certain condition is True or False.
* You can use the IFS function when you have multiple conditions that are not exact matches (for example < or >).
* For the Microsoft documentation refer to support.microsoft.com

 AB
1=SWITCH(B4,"one",1,"two",2,"three",3,"four",4,5) = 19
2=IFS(B4="one",1,B4="two",2,B4="three",3,B4="four",4,ISTEXT(B4)=TRUE,5) = 114
3=IF(B4="one",1,IF(B4="two",2,IF(B4="three",3,IF(B4="four",4,5)))) = 141
4=SWITCH(1,1,"London",2,"New York") = Londonone
5=SWITCH(2,1,"London",2,"New York") = New Yorktwo
6=SWITCH("Tokyo","London",1,"Tokyo",2) = 2 
7=SWITCH("TOKYO","London",1,"Tokyo",2) = 2 
8=SWITCH("Tokyo","London",1,"Tokyo",2,"Tokyo",3) = 2 
9=SWITCH("findtext",1,"London",2,"New York","no match") = no match 
10=SWITCH("findtext",1,"London",2,"New York") = #N/A 
11=SWITCH(3,1,"London",2,"Paris") = #N/A 

1 - What value is returned when the cell "B4" is compared to this list of exact matches.
2 - What would the equivalent formula be using the IFS function
3 - What would the equivalent formula be using multiple nested IF functions.
4 - What is the first result where the value matches the number "1".
5 - What is the first result where the value matches the number "2".
6 - What is the first result where the value matches the string "Tokyo".
7 - What is the first result where the value matches the string "TOKYO". This function is not case sensitive.
8 - What is the first result where the value matches the string "Tokyo".
9 - What is the first result where the value matches the string "findtext". The value is not found so the default value is returned.
10 - What is the first result where the value matches the string "findtext". The value is not found and no default value has been provided.
11 - What is the first result where the value matches the number "3". The value is not found and no default value has been provided.

© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited Top