Duration : 90 Minutes
Mastering Excel Formulas VLOOKUP, MATCH/INDEX, SUMIF, SUMIFS, and More
VLOOKUP is often the first look-up function that savy Excel users learn. In this session Excel expert David H. Ringstrom, CPA goes beyond the basics to reveal the quirks, nuances, and limitations of VLOOKUP. You'll then see how to go beyond VLOOKUP with alternatives such as MATCH/INDEX, SUMIF, SUMIFS, and more.
David Ringstrom’s courses are fast-paced, and attendees are often surprised at the amount of ground that he can cover in a session. David welcomes your questions, so come ready to pick his brain. His detailed handouts provide reference material you can fall back on, and he also provides an Excel workbook that contains most of the examples that he teaches from during the session.
Topics typically covered:
- Avoid the complexity of nested IF statements with Excel's CHOOSE function
- Discover how to use wildcards and multiple criteria within look-up formulas
- Don't be caught off-guard by the new IFNA function available to Excel 2013 users
- Future-proof VLOOKUP by using Excel's Table feature instead of referencing static ranges
- Improve the integrity of spreadsheets with Excel's VLOOKUP function
- Perform dual lookups, where you look across columns and down rows to cross reference the data you need
- See why the MATCH and INDEX combination is often superior to VLOOKUP or HLOOKUP
- Uncover the limitations of VLOOKUP, along with alternatives
- Use the SUMIF function to summarize data based on a single criterion
- Use the SUMIFS to sum value based on multiple criteria
- Determine situations where VLOOKUP may return an error or omit desired data
- Improve the integrity of VLOOKUP by way of the MATCH function and/or Excel's Table feature
- Utilize the SUMIFS function in Excel 2007 and later for multiple-criteria look-ups
Who should attend: Practitioners who rely on look-up functions within their spreadsheets.