Excel Magic Trick 1287: SWITCH Function: How to Lookup Formulas, References, or Anything Else



Online Courses for microsoft excel :

Course updated 2016-03-05 15:23:58

Instructor : ExcelIsFun

Course Duration : 17:52

Rating : 4.81 / 5

Step by step & Descreption :
Download File:
Learn how to:

1. (00:11) Intro to Exact Match lookup SWITCH Function, including a default value. SWITCH in a New Excel 2016 function.
2. (01:13) SWITCH to lookup Text Items and put a Default Value in the cell if the lookup value is not found.
3. (02:52) Compare and contract IFS function, CHOOSE function, VLOOKUP function and SWITCH function
4. (04:44) See how to use VLOOKUP and the IFNA Function with a hard coded lookup table in your formula when you have a lookup situation and you need a Default Value rather than SWITCH to make a more compact and efficient formula.
5. (06:24) When to NEVER use SWITCH
6. (06:35) SWITCH to lookup formulas. Credit Assessment formulas to gage credit worthiness.
7. (09:48) What formula do you use when you have multiple lookup tables?
8. (10:18) Multiple lookup tables example 1: CHOOSE, VLOOKUP, IFNA, and VLOOKUP. This is the method we used before we had SWITCH
9. (13:34) Multiple lookup tables example 2: SWITCH and VLOOKUP to create a much shorter formula than using CHOOSE. This example shows that SWITCH requires hard coded values in the valueN argument (which Microsoft says is a bug and will be fixed soon (this is written on 3/5/2016).
10. (15:54) Bug as of 3/5/2016 for SWITCH: ValueN argument cannot handle references in the argument to lookup references. Microsoft says they will fix this bug.
11. (17:02) Summary of uses for SWITCH
Reference Videos:
Highline Excel 2013 Class Video 14: AND, OR, IF Functions For AR Dept (Multiple Logical Tests)

Purchase “Office 2016 for Office 365”
Go to Office Insider site to download “Office Insider Version”
Office Insider Version: 16.0.6568.1011 or 16.0.6568.2025