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

SHARE
Previous articleWebinars : Scalable Continuous Integration with Bamboo – Webinar
Next articleWebinars : Admin Quick Start Guide – Conference Tracker Webinar Series
Over 2800 Excel How To Videos from Beginner to Advanced. Mike "excelisfun channel" Girvin is a business instructor at Highline Community College and an Excel MVP in the years 2013-2016. He has created over 2800 Free Excel How To Videos and over 50 playlist of videos. Excelisfun has also authored two Excel books and three DVDs. Hopefully these videos will help you to become better with Excel so you can save time at work, help coworkers become better at Excel and save so much time that you can take an extra vacation!