Excel Magic Trick 1287: SWITCH Function: How to Lookup Formulas, References, or Anything Else
Download File:
http://people.highline.edu/mgirvin/excelisfun
.htm
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)
https://www.youtube.com/watch?v=SennYjw9yGQ
Purchase "
Office 2016 for
Office 365" https://products.office.com/en-us/buy/office
Go to
Office Insider site to download "Office Insider
Version" https://products.office.com/en-US/office-insider
Office Insider Version: 16.0.6568.1011 or 16.0.6568.2025