1 - 2 CPE 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: March 10, 2024
Included In Certifications
This course is included in the following Certification Programs:
17 CoursesExcel Modeler Certification
- Excel Efficiency: VLOOKUP
- Excel Efficiency: Logic Functions
- Excel Efficiency: Filtering and Formatting Data
- Excel Efficiency: Intermediate Pivot Tables
- Excel Efficiency: Auditing Spreadsheets
- Excel Efficiency: Minimizing Worksheet Errors
- Excel Efficiency: Workbook Links
- Excel Efficiency: What-If Analysis in Microsoft Excel
- Excel Efficiency: Quick and Easy Financial Statements in Excel
- Excel Efficiency: Budget Spreadsheets
- Hands-On Excel: Waterfall Calculations
- Excel Efficiency: Table Feature
- Excel Efficiency: Intro to Macros Part 1
- Excel Efficiency: Intro to Macros Part 2
- Excel Efficiency: Excel Chart Speed Tips
- Excel Efficiency: Taming Large Spreadsheets
- Excel Efficiency: Internal Controls
139 Reviews (527 ratings)
Reviews
Bertha Mujica
( at Mujica)
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.
Karen Schrader
(Sr Accountant Lead at Charter Communications)
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!
Anonymous
(Director)
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.
Kent Smith
( at Synapse Product Development)
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
Nick Wright
( at WCF Insurance)
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.
Anonymous
()
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.
Rose Gingrich
(State Report at GCCS)
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
Anonymous
(Accountant)
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.
Jessica Spaulding
(Accountant at Firebirds Funding)
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.
Anonymous
()
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.
George Laning
(CPA at George J. Laning CPA)
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.
Cheryl Leide
(Financial Systems Manager at Wolseley Canada)
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.
Anonymous
(VP Finance)
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.
Anonymous
(AP Specialist - Payments Team)
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.
Anonymous
(Independent Financial Consultant)
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.
Anonymous
(Sr. Accountant)
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.
Anonymous
(Senior Lean Development Officer)
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
Anonymous
()
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.
Michael Zomber
( at MOCFS)
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.
James Robinson
(CFO at NHS LA COUNTY)
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.
Anonymous
(Corporate Controller)
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.
Norman West
(Financial Manager at Lockheed Martin)
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.
Anonymous
(APS)
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.
Christopher Madsen
(Manager/Corporate Planning at Southwest Gas Corporation)
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.
Anonymous
(Accountant)
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.
Anonymous
(controller)
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.
Anonymous
()
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
Carmen Cruz
(Property Accounting Manager at AAC Realty)
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.
Anonymous
(Business Analyst)
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.
Anonymous
(Senior Accounting & Reporting An..)
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.
Kayla Garbison
(Account Executive at Yelp)
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.
Anonymous
(Director)
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.
Anonymous
()
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!
Tony Gardella
(Sourcing Specialist at Intuit)
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!
Anonymous
(Assistant Controller)
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.
Oxana Pelc
(Access Management Specialist at Hire Counsel @Facebook)
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.
Cathy Kyriakis
(Distribution & Sales Ops Mgr at Intuit Canada)
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.
Anonymous
(Consultant)
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.
Stephanie Woodworth
(Staff Accountant at Yelp)
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.
Anonymous
(Research Service Analyst)
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.
Anonymous
(Consultant)
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!
Anonymous
(CPA / Owner)
Excellent instructor and course materials. Demonstrates the advantage/disadvantage of one function vs another for accomplishing a chosen objective.
Anonymous
(Associate Accountant)
This course was sufficient for my needs. The material was hard and confusing but the course materials provided really helped me understand better.
Anonymous
(IT Advisory Snr. Associate)
A great introductory course to V-lookup. Nothing surprised me though. This will be beneficial to anyone interested in the excel v-lookup function.
JIN ZOU
(Senior Accountant at STS Traders)
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.
Anonymous
(Accounting & Finance Assoc)
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.
Cynthia Quigley
(Accountant at Steinhatchee River Club)
This is my second course from this instructor. Having the slides and the workbook to follow along really helps understand the material.
Jatin Talreja
(Financial Accounting Consultant at Wells Fargo)
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
Anonymous
(Card Program Administrator)
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.
Judith Ortiz Villar
(Consultant at RSM)
I enjoyed this course; however, I believe basic background knowledge is needed in order to follow, due to the pace of the training.
Anonymous
(Financial Reporting Manager)
Very good explanations of various functions, including VLOOKUP, SUMIF, and SUMIFS. Explains the advantages and limitations of each.
Anonymous
(Senior Accountant)
Great coarse. I have limited experience with VLOOKUP but was able to get a good understanding of what the function can perform.
Alison Pritchcard
(Accounts Payable Specialist at Veeva Systems Inc)
Thank you David for your thorough VLookup training...it was easy to follow and I appreciated all of the examples / scenarios.
Anonymous
(Owner)
This course is very helpful in both understanding vlookup and related functions as well as resolving some errors in formulas.
Anthony Darakjy
(Associate at Methuselah Advisors)
David is actually a very good teacher - keeps his language simple and good examples to follow. Great for simplifying excel
Karin Hill
(Settlement Supervisor at First Interstate Bank)
note for instructor: lesson VLOOKUP with IFNA repeats from 2:35 to the end, and did not show the demo of the IFNA function
Anonymous
(Senior Accountant)
It was a good course, I wish I was a little more familiar with Vlookup in advance - there are many formulas to remember
Jon Koepke
(Consultant at in transition)
Would be nice to mention about Hlookup's and any differences. Great refresher course after not using them for a while
Joseph Stevens
( at O'Neil & Associates)
This is a great course for understanding the various formula options within Excel. I'm surprised how much it covered.
Deanna Reed
(Accountant at Charter Communications)
Nice overview, would be nice to have a companion workbook to be able to work through examples with the instructor.
Sheila Mehta
(N/A at PSSI group)
relevant and useful material. chapters were well organized and defined. Quiz was helpful and Exam encompassing.
Nidhi Sharma
(SBA at Intuit)
vlookup explained very well. Some other functions were introduced too which may not be applicable for everyone.
Anonymous
(Wireless Data Analyst)
The lesson was detail and gave great examples and explanations and went over the exercises a couple times.
Anonymous
(Controller)
Good examples showing the details of each formula and explaining what each part of the formula represents
Jeff Bone
(Financial Advisor at Northwestern Mutual)
This course content could have been provided in a more clear/concise manner, but still got the job done.
Michael O'Malley
(Supervisor at RSM US LLP)
Difficult to follow instructor as mutliple screen shots are applied to each slide in the presentation
Maxwell Hassenstein
( at NeoGenomics)
Great material and presentation. I really enjoyed learning about INDEX and MATCH as an alternative.
Anonymous
()
Good refresher on Vlookup, index match, sumif/s, etc. Didn't see anything on hlookup. Overall good.
Anonymous
(Consulting Staff - Energy Accounting Sol..)
I liked that the instructor went over each topic and then followed it up with an example in Excel.
Anonymous
(Senior Accountant)
Large amount of material covered for a relative novice to vlookups. Good information provided.
Anonymous
(solo)
Great course! Lots of examples, with separate worksheets to illustrate the incremental steps.
Deepti Bhatnagar , CISA, CRMA
(Senior Manager, Compliance at Cisco Systems Inc.)
Very helpful course in learning how to use VLOOKUP and also making the usage more efficient.
JESSICA MCKERNAN
(Senior Accountant at Penumbra)
I didn't expect to learn anything new, but I didn't previously know about the IFNA formula.
Anonymous
(Finance Analyst)
Excellent - I picked up many tips on IF(ISNA, data valuation lists, match, index and choose.
Anonymous
(Staff Accountant)
The course was informational and provided examples and steps to help you complete the task.
Declan Harte
(Financial Analyst III at Centene Corporation)
Great overview of vlookup function, its good and bad points, and the alternatives to it.
Bob Phipps
( at R.W. Phipps, PA)
i love the amount of excel sheets for us to test them out, depending on the circumstance
Anonymous
()
This was a very informative course. I learned a lot of great tools i can use in excel.
Anonymous
(Associate Director, CFCC Assurance)
Great course and very informative! Take it if you need a good refresher of VLOOKUPS.
Anonymous
(Order Process Rep)
I learned a lot of formulas or short cuts within this course. Very good information.
Sharniece Ford
(Accountant at Charter Communications)
the course was very informative and taught me a lot regarding the vlookup function.
Anonymous
()
Great refresher to VLookup and other formulas, this will help with working in excel
Anonymous
(Consultant - Process Specialist)
This is very concise. I thought I knew everything about vlookup, but I was wrong.
George Bilyeu
(Senior Accountant at Bluestreet Solutions, Inc.)
A bit hard to follow in some places but overall, another very good presentation
Anonymous
(UP Specialist)
David is a great presenter. he is easy to follow and know excel inside and out
Anonymous
(Senior Info Sec Analyst)
I find it difficult to understand the lecture. it was little unclear and tough.
Anonymous
(Billing Analyst)
It would be helpful to have practice materials available. I created by own.
Karen Fischer
(Accounting Manager at Christus Health)
Moves a little too fast through steps for people new to using the functions
Anonymous
(Treasury Director)
Awesome course, even if you think you know how to use the VLOOKUP formula.
Anonymous
(Chief Financial Officer)
Very good as either initial introduction to the material or as a refresher.
Jeff Klein
(Capital Programs and Business Analyst at National World War II Museum)
Excellent course, good pacing. I was able to keep up with the instructions
Anonymous
(Director of Cloud Solutions)
The course was very informative and I would recommend it to my colleges.
Xinlu Wen
(Associate at PwC China)
Very useful courses to utilize the function of vlookup and indexmatch
Anonymous
(Manager)
very informative a lot of information covered, gave many new options
Nicholas Novelly
(CFO at St. Mary's Academy &amp..)
I learned a lot of new things and I have used Excel for a long time.
Janey Edwards
(Accounting Manager at Silicon Valley Finance Group)
Very helpful in understanding Vlookup and the nuances and errors.
Radhesh Pradhan
(Accounting Manager at DGN Technologies Inc)
Very useful course. David is a magician when it comes to excel.
Tonya Muszall
(Associate Accountant at Charter)
great instruction, easy to follow, diagrams help tremendously!
Anonymous
(Research Operations and Financial Office..)
This is a very detail and step by step course. Easy to follow.
Anonymous
(Accountant)
very well explained and all details presented for each chapter
Ryan O'Donnell
( at Maiden Holdings, Ltd)
Very useful course for those who use excel on a regular basis.
Christopher Maxwell
(Sr. Manager - Learning & Develop.. at Charter Communications)
Great course. Lots of useful features explained in detail.
Anonymous
(accountant)
Oustanding work! Love the way he breaks down the information.
Anonymous
()
Great refresher on a function I use all the time. So useful!
Anonymous
()
Useful information for lookups, a great refresher - thanks!
Anonymous
(Accountant)
VLOOKUP is great! Thank you for a very informative course.
Anonymous
(Manager)
I think more examples would've been great to practice with
Anonymous
(CPA)
Made me aware of additional options when using VLOOKUP.
Beth Manders
(Senior Billing Coordinator at DCI)
I liked that there were a lot of extra tools to use
.
Vera Nickelsen
(Fraud Agent at Intuit)
great info. easy to follow and apply to what I do.
Anonymous
(Salaried Consultant)
A good refresh and some new tricks towards the end.
Jesse Smith
(Accounts Receivable at Yelp)
Lots of information to intake. Helpful information!
Anonymous
(Compliance)
Good Training. Objective were completed. Thank you.
Brad Hyatt
(Finance Systems Manager at State Auto Insurance)
Great class to get familiar with these functions
Anonymous
(Consulant)
Need more spreadsheets to practice formulas on
Anonymous
(Sr. Director, FP&A)
A bit basic but I still learned a few tricks.
Matt Osmundsen
(Category Finance Director at Energizer Holdings, Inc.)
Great course! Very informative and helpful.
Anonymous
(Group Internal Audit Officer)
Easy and understandable training. Thank you
Wayne Muscarello
(Finance PMO Manager at Direct Energy)
Very good primer for the Vlookup function.
Stacie Sebring
(Accountant at Not working)
David is great at teaching Excel courses.
Anonymous
(General Ledger Accountant)
Learn new useful functions of vlookup.
Anonymous
(cfo - consultant)
A simple process for a powerful tool.
Chris Grimes
(Rules Master at Cisco)
Good overview of the vlookup function
Anonymous
(Assurance Manager)
Good info to put to use immediately
Cheryl Honeycutt
(Controller at MillerWeisbrod LLP)
Very helpful tips using Excel
Shelly Woods
(Market Controller at KTXS News)
clear slides and instruction
Anonymous
(Accounting Manager)
Very insightful subject
Jaclyn DiCostanzi
(Admin at Kuehne Company)
it was informative.
Thabit Ndilahomba
(Head of Finance at Brac)
I like the course
Martin Clancy
(CFO at DataCore Software)
A good refresher.
Anonymous
(Controller)
Nice refresher
Anonymous
(Farm and Nursery Manager)
Decent course
Siva Vinay
(Controller at Park n Fly)
Great detao;s
Sheilah Brown
(Controller at SMB LTC)
Good review.
Anonymous
(Senior Director & Controller, Financ..)
great course
Anonymous
(VP, Finance)
Good course
Anonymous
(Senior Internal Auditor)
Good Course
Anonymous
(Technical Accountant)
Good
Anonymous
(Consultant)
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
.
David RingstromCPA, author, Microsoft Excel trainer and consultant
About Illumeo
Platform
Resources
Contact Us
- Email: info@illumeo.com
- Phone: 408-400-3993
- Adress: 1608 W Campbell
- Av #221, Campbell, CA 95008
© 2024 Illumeo, Inc.