Excel Magic Trick 1287


SWITCH Function: How to Lookup Formulas, References, or Anything Else
Learn how to:
(00:11) Intro to Exact Match lookup SWITCH Function, including a default value. SWITCH in a New Excel 2016 function.
(01:13) SWITCH to lookup Text Items and put a Default Value in the cell if the lookup value is not found.
(02:52) Compare and contract IFS function, CHOOSE function, VLOOKUP function and SWITCH function
(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.
(06:24) When to NEVER use SWITCH
(06:35) SWITCH to lookup formulas. Credit Assessment formulas to gage credit worthiness.
(09:48) What formula do you use when you have multiple lookup tables?
(10:18) Multiple lookup tables example 1: CHOOSE, VLOOKUP, IFNA, and VLOOKUP. This is the method we used before we had SWITCH
(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).
(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.
(17:02) Summary of uses for SWITCH


Download “Excel Magic Trick 1281-1300.zip” Excel-Magic-Trick-1281-1300.zip – Downloaded 179 times – 4 MB

Be the first to comment

Leave a Reply