Instructor for this course
more

Excel expert David Ringstrom, CPA, explains helpful ways you can improve the integrity of your spreadsheets using Excel’s lookup functions. The presentation leads with a comparsion of exact and approximate matches with VLOOKUP, and then comparisons to the HLOOKUP and LOOKUP functions. The presentation then covers a variety of troubleshooting techniques for VLOOKUP, and wraps up with a discussion of other alternatives such as MATCH/INDEX, SUMIF, and SUMIFS. David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2016. He draws your attention to any differences in Excel 2013, 2010, or 2007 during the presentation as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples he uses during the webcast. Topics covered:

  • Using the MATCH function to find the position of an item in a list
  • Utilizing Excel’s IFERROR function to display alternate values when VLOOKUP returns an error
  • Removing the Table feature from a worksheet if it’s no longer needed
  • Contrasting the INDEX and MATCH combination to VLOOKUP or HLOOKUP
  • Improving the integrity of spreadsheets with Excel’s VLOOKUP function
  • Avoiding the complexity of nested IF statements with Excel’s CHOOSE function
  • Future-proofing VLOOKUP by using Excel’s Table feature versus referencing static ranges
  • Enabling VLOOKUP to look up data from the left (instead of only from the right) by using the CHOOSE function
  • Transforming numbers stored as text into values by way of the Text to Columns wizard
  • Learning about the IFNA function available in Excel 2013 and later
  • Identifying situations where VLOOKUP may return #N/A instead of a value

Learning Objectives

  • Identify what you can use in place of the word TRUE in VLOOKUP to return an approximate match.
  • Recognize the maximum number of criteria pairs that the SUMIFS function permits.
  • Explore the purpose of the IFERROR function
Last updated/reviewed: July 6, 2018

58 Reviews (171 ratings)Reviews

3
Member's Profile
Stated learning objectives were met Stated prerequisite requirements were appropriate and sufficient Program materials were relevant and contributed to the achievement of the learning objectives Time allotted to the learning activity was appropriate Instructor(s) was effective
5
Anonymous Author
I've taken several excel courses from David Ringstrom and they are always informative. In this one, the slides are very busy so not easy to use as a reference going forward, but better than some of his other courses.
4
Anonymous Author
I use VLOOKUP periodically in my job and I used to have to find an old spreadsheet to remember the sequence of values in the formula. Not anymore. Now i just go back to David's presentation for a quick refresher.
4
Anonymous Author
Excellent intro (or review) of VLOOKUP. The explanations of the various nuances of VLOOKUP were clear and well-explained. The examples provided in the presentation materials definitely enhanced the presentation.
4
Anonymous Author
Useful and powerful tools simply explained. Some syntax needs to be practiced to ensure proper results and trouble shooting longer or more complicated commands can be troublesome but definitely worth the effort
5
Member's Profile
I don't use vlookup often but seem to have issues when I do. This lesson gave me a better understanding of the function. I can see it is useful for a number of purposes. Outstanding presentation.
4
Member's Profile
The information was good, but there was quite a bit of repetition. With access to the course and reference material, I can come back later. I don't need the same thing repeated three times.
5
Anonymous Author
Nice thorough explanations and examples of using vlookup. This course also showed common errors and restraints of using vlookup and how to avoid and fix them. Good course overall.
4
Anonymous Author
I did not think the question about what version of excel a formula is first available in was very interesting or challenging information. There is an error on slide 2606.
5
Member's Profile
Very helpful course! I have trouble with errors quite often using Vlookup. This presentation provided great tips and tricks to prevent further errors from happening.
5
Member's Profile
Very helpful! I've always felt VLOOKUP was a monster and didn't know how or where to start learning it. Now I feel like I'm prepared to use it in my everyday work!
5
Member's Profile
The course was very informative but a bit too fast paced for an individual who is not an expert in Excel. Great resources are available for future references.
3
Member's Profile
A good refresher for Vlookup, however, The IFNA Function module seems to have completely repeated itself and is twice as long as the information actually is.
5
Anonymous Author
This course was extremely helpful. I learned that there are many more functions that can be combined with VLOOKUP to get more accurate results in Excel.
4
Anonymous Author
There was a lot of repeated data given. Made the course feel dragged out. Also slides had typos and/or wrong info stated in few places.
4
Member's Profile
I enjoyed this course; however, I believe basic background knowledge is needed in order to follow, due to the pace of the training.
4
Member's Profile
note for instructor: lesson VLOOKUP with IFNA repeats from 2:35 to the end, and did not show the demo of the IFNA function
5
Member's Profile
Would be nice to mention about Hlookup's and any differences. Great refresher course after not using them for a while
4
Member's Profile
vlookup explained very well. Some other functions were introduced too which may not be applicable for everyone.
3
Member's Profile
This course content could have been provided in a more clear/concise manner, but still got the job done.
3
Member's Profile
Difficult to follow instructor as mutliple screen shots are applied to each slide in the presentation
5
Anonymous Author
I liked that the instructor went over each topic and then followed it up with an example in Excel.
4
Member's Profile
Very helpful course in learning how to use VLOOKUP and also making the usage more efficient.
5
Member's Profile
I didn't expect to learn anything new, but I didn't previously know about the IFNA formula.
5
Member's Profile
Great overview of vlookup function, its good and bad points, and the alternatives to it.
5
Anonymous Author
I learned a lot of formulas or short cuts within this course. Very good information.
4
Member's Profile
A bit hard to follow in some places but overall, another very good presentation
5
Member's Profile
Very helpful in understanding Vlookup and the nuances and errors.
5
Member's Profile
Very useful course. David is a magician when it comes to excel.
5
Anonymous Author
Made me aware of additional options when using VLOOKUP.
5
Member's Profile
great info. easy to follow and apply to what I do.
5
Anonymous Author
A good refresh and some new tricks towards the end.
5
Member's Profile
Great class to get familiar with these functions
4
Anonymous Author
Need more spreadsheets to practice formulas on
4
Anonymous Author
A bit basic but I still learned a few tricks.
5
Member's Profile
Great course! Very informative and helpful.
4
Anonymous Author
Easy and understandable training. Thank you
5
Member's Profile
Very good primer for the Vlookup function.
5
Member's Profile
David is great at teaching Excel courses.
5
Anonymous Author
Learn new useful functions of vlookup.
5
Anonymous Author
A simple process for a powerful tool.
5
Member's Profile
Good overview of the vlookup function
4
Anonymous Author
Good info to put to use immediately
4
Member's Profile
Very helpful tips using Excel
4
Member's Profile
clear slides and instruction
4
Anonymous Author
Very insightful subject
3
Member's Profile
it was informative.
4
Member's Profile
I like the course
4
Member's Profile
A good refresher.
3
Anonymous Author
Nice refresher
5
Anonymous Author
Decent course
5
Member's Profile
Great detao;s
4
Member's Profile
Good review.
4
Anonymous Author
great course
4
Anonymous Author
Good course
5
Anonymous Author
Good Course
5
Anonymous Author
Good
4
Anonymous Author
Good

Prerequisites

Course Complexity: Intermediate

Prerequisite: Experience with Lookup Formulas Recommended

Advanced Preparation: None

 

Education Provider Information

Company:
Illumeo, Inc., 75 East Santa Clara St., Suite 1215, San Jose, CA 95113
Contact:
For more information regarding this course, including complaint and cancellation policies, please contact our offices at (408) 400- 3993 or send an e-mail to .
Course Syllabus
INTRODUCTION AND OVERVIEW
  8:24Introduction to VLOOKUP
Excel Efficiency: VLOOKUP
  8:57VLOOKUP - Approximate Match
  10:04Introduction to LOOKUP
  10:10VLOOKUP - Data Validation
  10:36VLOOKUP - #REF! Error
  9:18VLOOKUP - Text vs. Numbers
  8:09Using the Table Feature with VLOOKUP
  9:14MATCH/INDEX Example
  9:18Introduction to the CHOOSE Function
  8:33SUMIF Function
  8:57SUMIFS Function with One Range Criteria
Continuous Play
  1:41:39Excel Efficiency: VLOOKUP
SUPPORTING MATERIALS
  PDFExcel Efficiency VLOOKUP-Slides
  PDFExcel Efficiency: VLOOKUP Glossary/Index
  XLSXWorkbook: Excel Efficiency: VLOOKUP
REVIEW & TEST
  quizREVIEW QUESTIONS
 examFINAL EXAM