Beyond VLOOKUP (Currently Unavailable)

Author: David H. Ringstrom

Many users rely on VLOOKUP for basic look-up functionality in spreadsheets, but are often unaware of ways to improve the integrity of this venerable function. In this session Excel expert David H. Ringstrom, CPA introduces the VLOOKUP function, and then quickly goes beyond the basics. Discover what can go awry with VLOOKUP, how to future-proof the function, and explore alternatives such as MATCH/INDEX, SUMIF, SUMIFS, SMALL/LARGE, MIN/MAX, and OFFSET.

David’s materials cover Excel 2016, 2013, 2010, and 2007. When applicable, some slides include alternate instructions for Excel 2003. In this presentation, he demonstrates techniques in Excel 2010, as he finds it’s presently the version most widely utilized by Excel users. David’s detailed handouts serve as reference tools you can fall back on after completing one of his courses. He also provides an Excel workbook that includes a majority of the examples he uses during each session.

Publication Date: October 2015

Designed For
Practitioners who rely on look-up functions within their spreadsheets.

Topics Covered

• Avoid the complexity of nested IF statements with Excel's CHOOSE function.
• Compare the MIN, SMALL, MAX and LARGE functions.
• Compare the IFERROR and IFNA worksheet functions.
• 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 how to arrange two worksheets from within the same workbook onscreen at the same time.
• See why the MATCH and INDEX combination is often superior to VLOOKUP or HLOOKUP
• Use Excel's OFFSET function to dynamically reference data from one or more accounting periods.
• Use the SUMIF function to summarize data based on a single criterion.
• Use the SUMIFS to sum value based on multiple criteria.

Learning Objectives

• Identify which versions of Excel support the IFNA worksheet function.
• Identify two common # sign errors that VLOOKUP can trigger.
• Define uses of the OFFSET worksheet function.

Level
Intermediate

Instructional Method
Self-Study

NASBA Field of Study
Computer Software & Applications (3 hours)

Program Prerequisites
None

Advance Preparation
None