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: August 17, 2023

Included In Certifications

This course is included in the following Certification Programs:

17 CoursesExcel Modeler Certification

  1. Excel Efficiency: VLOOKUP
  2. Excel Efficiency: Logic Functions
  3. Excel Efficiency: Filtering and Formatting Data
  4. Excel Efficiency: Intermediate Pivot Tables
  5. Excel Efficiency: Auditing Spreadsheets
  6. Excel Efficiency: Minimizing Worksheet Errors
  7. Excel Efficiency: Workbook Links
  8. Excel Efficiency: What-If Analysis in Microsoft Excel
  9. Excel Efficiency: Quick and Easy Financial Statements in Excel
  10. Excel Efficiency: Budget Spreadsheets
  11. Hands-On Excel: Waterfall Calculations
  12. Excel Efficiency: Table Feature
  13. Excel Efficiency: Intro to Macros Part 1
  14. Excel Efficiency: Intro to Macros Part 2
  15. Excel Efficiency: Excel Chart Speed Tips
  16. Excel Efficiency: Taming Large Spreadsheets
  17. Excel Efficiency: Internal Controls
136 Reviews (506 ratings)

Reviews

5
Anonymous Author
This course definitely strengthened and expanded my VLOOKUP skill and knowledge. David Ringstrom presents the material in a clear manner at a good pace---not too fast and not too drawn out. His slides are always excellent too.

5
Member's Profile
I learned so much in this training. I thought I knew all about VLOOKUP, but I picked up some very useful information that’ll save me some time at my job. I wasn’t familiar with the wildcards, and I hadn’t ever used arguments in my SUMIF formulas. I really liked his way of explaining the formulas too. It made it really easy to understand the MATCH and INDEX formulas. I’m definitely saving the slides for future reference.

5
Member's Profile
Great in depth course on VLOOKUP, IFERROR, MATCH, CHOOSE, SUMIF, SUMIFS, etc. David is one of my favorite instructors for Illumeo. He speaks well, takes his time and after discussing each step he goes back and performs the example all at once. I would recommend this course to anyone who works with large files of data and needs to pull out specific information. Thanks David for another great course!

4
Anonymous Author
I am a heavy user of vlookup but I still find few items that I can apply to how I use the vlookup function including the use of * and reading a number as text as part of the vlookup. David is an effective instructor. I like his style of presentation. For new users of vlookup, this course will be helpful but will definitely require a lot of practice.

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
Member's Profile
Illumeo's CPE courses are some of the best on the market. Their service has a high focus on quality thanks to their professional instructors and peer reviewed courses. On top of that, they have a great price point and a fantastic support team for anyone interested in their continuing professional education.

4
Anonymous Author
Really liked the course - covered a lot of material. Would have liked there to be some practical examples for us to work through ourselves to better consolidate the knowledge but the workbook provided did have good examples that can be used for future reference.

5
Member's Profile
Thank you the wonderful instructor David Ringstrom, for put together all this information and explain it in the easy and professional way. I learn and enjoyed taking this course. If you want to improve your excel skill as a pro , take this course

5
Anonymous Author
This was a great course and will help me a lot with my future endeavors in the accounting field. My excel skills have been completely reinvented from taking this course and I am very excited to implement these skills in my own workplace.

5
Member's Profile
This was a new topic for me, so I appreciated the detail that was given about the VLOOKUP function. I also really appreciate the additional time spent on demonstrating other related functions and when to use one vs. another.

5
Member's Profile
I liked the fact that he explained the function and showed how it was used and then explained it again. It allowed me to continue through the material without stopping and rewinding when something wasn't initially understood.

5
Member's Profile
This was a very good look into the various lookup functions in Excel. It reviews not only VLOOKUP but also Index/Match, Choose, and SUMIF. David Ringstrom is a great instructor for Excel and makes things look easy to use.

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.

5
Anonymous Author
Overall very helpful learning experience, re-learning the tips & tricks on how to properly run VLookups in Excel. I think more examples on the IFError and exact matching criteria would have helped explain that more.

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

4
Anonymous Author
I enjoyed the class as I use these functions quite frequently. I find the combining of the match/index with v lookup function a bit cumbersome. However, I need more exposure to this type of application.

5
Member's Profile
Fantastic course. I though I knew Vlookups already but there was a lot more to learn. I would definitely reach out to the instructor in the future if I have questions about advanced excel functions.

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.

5
Anonymous Author
These courses contain a lot of great gold nuggets that can be used in every day work. I really like the course material and I can stay focus due to the way the course is laid out. Great course.

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.

3
Anonymous Author
Being new to the VLookUp process, I think it would have been easier to understand if at first the data was more simplified by only using two or three columns before adding additional data.

4
Member's Profile
This course is effective at meeting the objective conveyed in its title. Mr. Ringstrom does a good job of building upon concepts and sharing examples that aid in the learning process.

5
Anonymous Author
This course gave me a better understanding of VLOOKUPs, I never really used this function too much during school so it definitely was helpful to get this training to help with my work.

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
The CHOOSE function allows you to return a specified item from a list, but in certain cases, it also can be used to have VLOOKUP return data from the left of its criteria column

4
Member's Profile
Great job in explaining the Vlookup concepts. The slides are helpful for future reference although it would be more helpful if they elaborated in writing the differences.

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
Anonymous Author
Great refresher on vlookup! I also really enjoyed learning more about match/index and sumifs. Additionally, the explanations for why certain errors show up was interesting.

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.

4
Anonymous Author
Gave a very good introduction to vlookup's and how they worked. Then expanded on the additional excel commands that can be utilized as refinements to this command.

5
Anonymous Author
Great course. VLookup and the other functions shown are so powerful I'll need to remember them when I find the need to use them. Thanks for the well-designed course!

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
Anonymous Author
This course provided easy to follow examples of the formulas and how they can be used. The MATCH function within a Vlookup was new to me but will be very useful.

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
Excellent course with visuals to guide you through the VLOOKUP function. Having this understanding will bring efficiency to my day to day work at my job.

5
Member's Profile
Easy to follow along with this class and it updates you if you covered that section in the syllabus. David is very clear and give numerous helpful examples.

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.

5
Anonymous Author
I liked the easy explanations and the quality of the supporting materials. Having an excel workbook with quality examples is a huge plus. Thank you!

5
Anonymous Author
Excellent instructor and course materials. Demonstrates the advantage/disadvantage of one function vs another for accomplishing a chosen objective.

5
Anonymous Author
This course was sufficient for my needs. The material was hard and confusing but the course materials provided really helped me understand better.

5
Anonymous Author
A great introductory course to V-lookup. Nothing surprised me though. This will be beneficial to anyone interested in the excel v-lookup function.

5
Member's Profile
The course is a practical and concise review of the VLOOKUP, SUMIF, and SUMIFS functions. It teaches me some news skills I can apply to work.

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.

5
Member's Profile
This is my second course from this instructor. Having the slides and the workbook to follow along really helps understand the material.

5
Member's Profile
The tips and hacks used with vlookup were something that were both interesting and new for me . I look forward to use these in my work

5
Anonymous Author
I found this very interesting and was not aware of the many types of lookups offered in Excel. Can find several uses in my position.

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.

5
Anonymous Author
Very good explanations of various functions, including VLOOKUP, SUMIF, and SUMIFS. Explains the advantages and limitations of each.

5
Anonymous Author
Great coarse. I have limited experience with VLOOKUP but was able to get a good understanding of what the function can perform.

4
Member's Profile
Thank you David for your thorough VLookup training...it was easy to follow and I appreciated all of the examples / scenarios.

5
Anonymous Author
This course is very helpful in both understanding vlookup and related functions as well as resolving some errors in formulas.

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

5
Member's Profile
This is a great course for understanding the various formula options within Excel. I'm surprised how much it covered.

4
Member's Profile
Nice overview, would be nice to have a companion workbook to be able to work through examples with the instructor.

5
Member's Profile
relevant and useful material. chapters were well organized and defined. Quiz was helpful and Exam encompassing.

4
Member's Profile
vlookup explained very well. Some other functions were introduced too which may not be applicable for everyone.

5
Anonymous Author
The lesson was detail and gave great examples and explanations and went over the exercises a couple times.

5
Anonymous Author
Good examples showing the details of each formula and explaining what each part of the formula represents

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
Member's Profile
Great material and presentation. I really enjoyed learning about INDEX and MATCH as an alternative.

5
Anonymous Author
Good refresher on Vlookup, index match, sumif/s, etc. Didn't see anything on hlookup. Overall good.

5
Anonymous Author
I liked that the instructor went over each topic and then followed it up with an example in Excel.

3
Anonymous Author
Large amount of material covered for a relative novice to vlookups. Good information provided.

5
Anonymous Author
Great course! Lots of examples, with separate worksheets to illustrate the incremental steps.

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
Anonymous Author
Excellent - I picked up many tips on IF(ISNA, data valuation lists, match, index and choose.

4
Anonymous Author
The course was informational and provided examples and steps to help you complete the task.

5
Member's Profile
Great overview of vlookup function, its good and bad points, and the alternatives to it.

4
Member's Profile
i love the amount of excel sheets for us to test them out, depending on the circumstance

4
Anonymous Author
This was a very informative course. I learned a lot of great tools i can use in excel.

5
Anonymous Author
Great course and very informative! Take it if you need a good refresher of VLOOKUPS.

5
Anonymous Author
I learned a lot of formulas or short cuts within this course. Very good information.

5
Member's Profile
the course was very informative and taught me a lot regarding the vlookup function.

4
Anonymous Author
This is very concise. I thought I knew everything about vlookup, but I was wrong.

4
Member's Profile
A bit hard to follow in some places but overall, another very good presentation

5
Anonymous Author
David is a great presenter. he is easy to follow and know excel inside and out

4
Anonymous Author
I find it difficult to understand the lecture. it was little unclear and tough.

4
Anonymous Author
It would be helpful to have practice materials available. I created by own.

3
Member's Profile
Moves a little too fast through steps for people new to using the functions

5
Anonymous Author
Awesome course, even if you think you know how to use the VLOOKUP formula.

5
Anonymous Author
Very good as either initial introduction to the material or as a refresher.

5
Member's Profile
Excellent course, good pacing. I was able to keep up with the instructions

5
Anonymous Author
The course was very informative and I would recommend it to my colleges.

5
Member's Profile
Very useful courses to utilize the function of vlookup and indexmatch

4
Anonymous Author
very informative a lot of information covered, gave many new options

5
Member's Profile
I learned a lot of new things and I have used Excel for a long time.

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
Member's Profile
great instruction, easy to follow, diagrams help tremendously!

5
Anonymous Author
This is a very detail and step by step course. Easy to follow.

5
Anonymous Author
very well explained and all details presented for each chapter

5
Member's Profile
Very useful course for those who use excel on a regular basis.

5
Member's Profile
Great course. Lots of useful features explained in detail.

5
Anonymous Author
Oustanding work! Love the way he breaks down the information.

5
Anonymous Author
Great refresher on a function I use all the time. So useful!

5
Anonymous Author
Useful information for lookups, a great refresher - thanks!

5
Anonymous Author
VLOOKUP is great! Thank you for a very informative course.

3
Anonymous Author
I think more examples would've been great to practice with

5
Anonymous Author
Made me aware of additional options when using VLOOKUP.

5
Member's Profile
I liked that there were a lot of extra tools to use .

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.

3
Member's Profile
Lots of information to intake. Helpful information!

5
Anonymous Author
Good Training. Objective were completed. Thank you.

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 .
Instructor for this course
Course Syllabus
INTRODUCTION AND OVERVIEW
  Introduction to VLOOKUP8:24
Excel Efficiency: VLOOKUP
  VLOOKUP - Approximate Match8:57
  Introduction to LOOKUP10:04
  VLOOKUP - Data Validation10:10
  VLOOKUP - #REF! Error10:36
  VLOOKUP - Text vs. Numbers9:18
  Using the Table Feature with VLOOKUP8:09
  MATCH/INDEX Example9:14
  Introduction to the CHOOSE Function9:18
  SUMIF Function8:33
  SUMIFS Function with One Range Criteria8:57
Continuous Play
  Excel Efficiency: VLOOKUP1:41:39
SUPPORTING MATERIALS
  Excel Efficiency VLOOKUP-SlidesPDF
  Excel Efficiency: VLOOKUP Glossary/IndexPDF
  Workbook: Excel Efficiency: VLOOKUPXLSX
REVIEW & TEST
  REVIEW QUESTIONSquiz
 FINAL EXAMexam