Best Microsoft Excel Bloggers

Friday, July 24, 2020

SWITCH - New Excel Function

SWITCH  is the newest function that Excel introduced in Excel 2019 and Microsoft 365.
In my mind, it is a bit of a cross between an IF function and a VLOOKUP function.

compares one value against a list of values and then returns the first match it finds. The list of values can be referenced or typed in.

 In the screenshot below, I have 3 employees and their Employee Levels. Every Employee Level has a starting salary. For example, all Employee Level A personnel start out at $35,000 while Employee Level B 's salary starts at $40,000.

In this example, I am telling Excel to look at the value in cell B2 and see if it is equal to an A. If it is equal to an A, I want it to return a value of $35000 to cell C2. If it is not an A, I want Excel to look and see if it is a B. If it is a B then Excel will return the corresponding value of $40000. I hard coded these values in but you could have used absolute cell references as well.  You can also set a default value to use if there is no match.

If you are handy with text functions then incorporating SWITCH into them should be useful when doing an inventory list of parts and part numbers.

Ms. Excel- Resident Excel Geek