more
Jeff Lenning, Author of Excel University
Former "Big 6" auditor. Accounting and finance roles at global companies. Published Excel expert. California CPA Education Foundation Award for Instructor Excellence.
This instructor has 11 courses »To Access This Course:
Hello and welcome Excel fans everywhere to our heavyweight event!
In one corner, with an impressive 20-year career, introducing the reigning champion of lookup functions, VLOOKUP. In the other corner, quickly gaining popularity with users, a recently introduced powerhouse, SUMIFS. Both functions have their unique ins and outs and situations in which you would prefer one over the other.
In this course, we’ll witness an epic battle between these two Excel heavyweights and watch these rivals go head-to-head using real-world examples and a full explanation of each. Who will win? Join us and find out…
Learning Objectives
- Explore the arguments of the VLOOKUP function
- Identify the SUMIFS function arguments
- Recognize the differences between these two functions
Last updated/reviewed: November 17, 2020
128 Reviews (382 ratings)Reviews
Hyoe Nakagiri
(Seeking Career Shift Opportunities at Non-Affiliated)
I had already taken the Excellence in Excel series, so I found the first half to be a refresher - but the final portion was a very effective presentation of how nesting formulas can work wonders.
Jeff’s engaging style makes the course very easy to follow, as with the other courses.
Anonymous
(Senior Accountant)
Great detailed presentation of how to construct both formulas, the benefits of each, the shortfalls / non performance of each. Can use these immediately in my various reports.
Anonymous
(Accountant)
Very helpful course, well explained with examples. Helps in the dayly work
Steven Srisamai
(Senior Accountant at Halo Top Creamery)
What I really liked about this course was the fact Jeff explained the mechanics of both formulas as well as the reasons behind needing the formulas. Also, the comparison of formulas helped with my understanding of their strengths and limitations. What surprised me the most was that these formulas can be used together, becoming a really powerful tool. I do however wish I was able to practice these using this formulas during the lecture myself with a practice spreadsheet with dummy data. That is the only thing I did not like about this course. Other than that, great job Jeff!
Andrew Jurado
(Program Manager at Willdan Energy Solutions)
The course video itself was fine A++. My critique is the quiz.
The test questions were poorly worded. For example, when it asks about "performing a match", I wasn't sure at what point of the logic. VLOOKUP looks for an answer in the leftmost column, then spits out the number to the right. I interpreted this question as "after it has performed its match where does it go from there"? I was annoyed that I knew the answer, but I got it wrong because of the wording of the question.
Karen Schrader
(Sr Accountant Lead at Charter Communications)
This course was helpful in understanding when to use each function and how they differ from one another and how they can be used together. The step by step instruction was informative but I wish instead of repeating the instruction we were given a problem to solve by using the functions. Jeff made the course fun with his 'battle' reference. Anyone in Finance or who creates large worksheets with lots of data could benefit from this course and the use of these two functions.
Michael Kelly
(Reporting Analyst at Huntington Bank)
What I did not get from this course was comparing to different worksheets of data elements and trying to look for matches (comparing 2 different reports to validate that data agrees). Also, when the exam is finished and the passing grade has been received, it would be nice to see the questions again so we can see which ones may have been incorrect and going back and reviewing the data again.
Anonymous
(Sr. Mgr - Credit Card Accounting)
This course is geared towards beginner level excel users and provides a very details explanation of these two functions for users that are not familiar with them. Examples were a little long but were effective in showing how to use these functions, their differences, and limitations. Effective course, but material could have been covered in a shorter time period.
George Bruffey III
(CFO at Stellar Development Co)
Great Course! I've been using Vlookup for years and used Sumif occassionally. SUMIFS will revolutionize my future work in Excel (AND SAVE STEPS!) - Thanks so much. Also, nesting sumifs in Vlookup is another revolution for me. I expect this to really help me in the future. I highly recommend this course if you summarize data or use lookup features in Excel.
Rahman Ullah
(Senior Accountant at Cardtronics)
Jeff is a boss! Absolutely my favorite presenter in this platform. As far as the course > Awesome! I love his courses, because they usually revolve around Excel which most accountants use at least once everyday! This is a must watch course for anyone like me who has used both functions, but not sure WHEN to use one over the other OR both!
Joel Abbott
(Senior Accounting Analyst at UMB Financial Corporation)
This course gave me a better grasp of how to use the two functions VLOOKUP and SUMIFS and makes me feel more comfortable using them in my workpapers. The pace was not too tedious or rushed. It was not overcomplicated but demonstrated the fundamentals of the functions and relevant applications.
Christina Torres
(IA Coordinator at Loews Corporation)
I am surprised that SUMIFs helped more than VLOOKUP in some of the rounds, I don't really use SUMIFS but now im happy that i know what to use in certain data findings. Also, I think Illumeo should attach a excel practice so the student can follow the instructor during some of the exercises.
Vickie Jackson
(Accounting Manager at NewStar Sourcing and Service)
Excellent course. Jeff Lenning was very entertaining while informative. I've always used false on vlookup and didn't really understand when true should be used. I now understand the difference. I also know now that Sumifs is a powerful tool.
Maeve Grogan
(Accountant at Charter Communications)
I think this class was really helpful in explaining both functions. It was very clear when sumifs should be used vs. when vlookup should be used. I thought the practical applications were helpful in understanding the differences between the functions.
Melissa Blansit
(Tax Manager at EKS&H)
Thank you - great comparison of common formulas. Determining which formula is most effective for different sets of data is important to keep the size of spreadsheets down, manage data sets efficiently, and make the formulas easy to review.
Anonymous
()
I found this course especially informative. Sumifs can do a lot and combined they can do even more. I liked the instructor. He was very easy to follow and I liked that he goes over the formulas 2x to really understand it.
Anonymous
(Management Analyst)
The section where the instructor compares Vlookup with Sumifs is extremely helpful. It illuminates under what circumstance to utilize each function and how they can be paired to yield the results users are looking for.
Nate Sives, CIA, CAMS
(Staff Auditor II at FNCB Bank)
Very clever way to show comparison of the 2 and gave great real-world examples of when you would use each. Going over examples a few times was a bit repetitive, but for beginners would be necessary. Overall Great
Anonymous
(Accounting Manager)
I truly enjoyed this course. Learned everything I needed to know about these two Excel functions and had fun doing it! Jeff has a great sense of humor and keeps his audience engaged throughout!
Anonymous
()
Really great course. Succinct and instructive. I do wish the excel files used were available for practice after the course to really learn the material taught. Otherwise, I was very impressed.
Dominick Pariano
(Senior Auditor at Materion)
Awesome course for learning the differences and similarities between these two formulas. Had some familiarity with VLOOKUP but learned a little more and definitely learned a lot about SUMIFS.
Natasha Henry
(Payroll Associate at Cibo Technologies)
This course helped me understand SUMIFS better by learning the appropriate steps and column order. Knowing that I should first select the $$$ column and lock made following the logic easier.
Julie Hammeras CPA
(Sr. Manager, Transformation - ERP at Liberty Utilities)
This course really helped me to get a handle on the differences in the two functions. I hadn't realized how versatile SUMIFS was! As always, Jeff Lenning is great at breaking Excel down.
Anonymous
(Sr AP Specialist)
Instructor was very upbeat and thorough. Utilized all explanations of how to get the returned value looking for. I WILL be using SUMIFs a whole lot more now that I have taken this class.
Anonymous
(Manager, Conservatorship Operations)
By repeating all the exercises and formulas, it is now easier to remember the formulas. Practical summary of how to remember the formula versus the "code" is helpful. Clear and concise.
Sally Fonstein
(Corp Controller at Ustream, Inc.)
The explanations of the functions was very easy to understand. His presentation was great. I feel I have a solid understanding of how to apply the information in the presentation.
Rachael Smith
(Senior Internal Auditor at FJ Management)
I learned some valuable information, however, it makes me cranky if the materials (i.e. spreadsheets) used for instruction are not provided as a download so that I can follow along.
Kayla Garbison
(Account Executive at Yelp)
So much more entertaining than most of the Excel courses I have taken. Jeff Lenning made the functions easy to remember by relating them to the spreadsheet data. Great course!
Emily O'Reilly
(FA III at City of Detroit)
I would have liked to have had the worksheets to practice along with the instructor. Failing that it would have been nice to have the slides of the presentation to reference.
Anonymous
(None)
This course did a good job presenting the differences, strengths and weaknesses of both Vlookup and Sumifs. The approach was very pragmatic and applicable in the real world.
Antoinette Burns
(Financial Analyst at City of Detroit)
I think the course gave me a better understanding of both functions. I was very pleased with the examples and the way the instructor did the problems more than once.
Donald Petter
( at SELF)
Jeff makes a technical discussion a conversation which makes the details easier to understand. I would highly recommend this to anyone who uses the VLOOKUP function.
Teresa Spector
(Not Provided at Elsevier)
I loved this course. I didn't realize how to use the SUMIFS function and I will definitely use it now. I need to practice right away before I forget how to use it!
Timothy Halliday
(Consulting Senior at EKS&H)
Awesome course, I highly recommend it for people who want to know more about both these functions. I wish the examples would have been a little more complicated.
Narendran Selvakumar
(Manager - Corporate Audit Services | AC.. at Larsen & Toubro Limited)
The instructor explains the use of both the functions step by step and compares its benefits and shortfalls. This course is useful for advanced Excel users too.
Anonymous
(Tax Associate)
Great explanation of each function. Love the use of multiple different examples. I finally feel like I understand when and how to use these two functions.
Thomas Grimmig
(Senior Accountant at Charter Communications)
This was an awesome and valuable course. Learning the SUMIFS function will change my work efforts forever. Very thankful that I chose to take this course.
Anonymous
(Revenue Analyst)
Typically I've resorted to using VLOOKUP however, after completing this course, can see the value in substituting SUMIFS or using a combination of the two.
Anonymous
(Accounting Manager)
A helpful and informative course on V-look up and SUMIFS functions. A good precursor to have an in depth knowledge and mastering functional excell skills.
Anonymous
(Controller)
First time using SUMIFS, liked the comparison between both functions. I can now use either to make my workbooks present information better and save time!
Sheilah Brown
(Controller at SMB LTC)
I wish the example spreadsheets had been included as course materials so that we could try the functions and check our results. Otherwise, great course.
Karin Williams
(None at self)
I use vlookup and sumifs all the time, but had never thought about the differences. Thanks for illuminating them. I'll probably use sumifs more now!
Anonymous
()
Well taught explanation of these functions. But the examples and supporting materials are missing - adding them would be make this a 5 star course.
Anonymous
(AP Specialist)
This course very informative. Jeff made the class fun & east to listen to. He explained the processes in a way that was easy to understand.
Rod Streets
(Vice President - Finance at YESCO LLC)
Presenter was very easy to listen to, kept the material moving at a good pace to keep interest, and used very relevant real world examples.
Anonymous
(President)
Nested vlookup and sumifs potential. Will use often. New to Illumeo and thought course was superior other courses with other CPE vendors.
Jeff Bone
(Financial Advisor at Northwestern Mutual)
Great course, I did not know how easy the sumifs function is to use and is something I know will make my excel workbooks more efficient.
Eric Cox
(Senior Accountant at Mondo)
i use both a lot every day but still found this to be helpful! i actually never knew what the fourth argument in vlookup was all about.
Erika Phillips
(NA at NA)
liked the presentation, but there should be a link to the excel spreadsheets so that we can follow along and practice at the same time.
Emma Harrington
(Audtor at Carnival Corporation Internal Audit)
Great approach to talk through a couple of times and show how it works practically - also like the adding in of using both together
Anonymous
()
Subject matter was clearly explained. Would have liked access to the examples so that I could try them before getting the answers.
Katherine Hall
(Chief Financial Officer at Winstead PC)
Jeff kept the subject matter interesting and informative. I appreciated the review and consistent terminology used throughout.
Steve Brown
(Senior Auditor at Charter Communications)
The instructor was very effective in teaching these 2 functions and I believe this will be very helpful for me in the future.
Anonymous
()
The lecturer presents the information in an understandable and rational format. Very good examples with concepts reinforced.
Anonymous
(Accounting Solutions Senior)
I have always been worried about the VLOOKUP function and now I feel very comfortable with it. Thank you for a great class!
Anonymous
()
Good refresher on use of VLOOKUP and the "text" version of what's happening. I'm new to SUMIFS, so that was a good intro.
Anonymous
()
I learned SUMIFS function is more useful than VLOOKUPS in most cases which I used to use only VLOOKUPS. it's so amazing.
Anonymous
(Wireless Data Analyst)
The lesson was put together very well. The examples were on point and specifically when doing side by side comparison.
Anonymous
(Sr Director Consolidation)
Great examples ans comparisons. I could relate and think of ways to speed up everyday tasks using both in various ways.
Bruce Reed
(Chief Financial Officer at PracticeLink)
Fun and informative way to present the practical uses for two related functions. Glad he added the way to use together.
Anonymous
(CFO)
Great presentation. The plain language for remembering functions is just what I needed to get them to stick. Thanks!
Samuel Naccarato
(Associate Manager at Yelp Inc)
Knowing the differences between SUMIFS and VLOOKUP will be very helpful. Each have their strengths and limitations.
Lori Sullivan
(Consultant at Eli Lilly & Company)
The presenter was engaging and enthusiastic.
Samuel Gephart
(Controller at Landrum & Brown, Inc.)
This was presented very well. I use these functions often and still was able to learn some useful information.
Ted Feher
(Owner at Feher, PC (CPA))
Provides a very good explanation of the difference between the two functions and a unique way to combine them.
william teets
(division controller at squan)
Course was presented in an effective manner with the right amount of repetition, pace and context. Well done.
Corrina Edwards
( at Hilti, Inc.)
This course gives great examples on how to apply the Sumif and vlookup functions and when to use each one.
Jon Koepke
(Consultant at in transition)
very interesting to see them compared side by side. Makes it alot easier to use correct formula now
Anonymous
(Accounting Manager)
This was a very good course for excel user who are not familiar with the sumifs or VLookup functions.
Diana Shipe
( at Acuity healthcare)
This course was so very helpful. the way the instructor explained what the functions do was amazing.
Anonymous
(Senior Accountant)
good course, I liked Jeff's energy in presenting the material and it made the study more enjoyable.
Anonymous
()
A informative course and I learnt a few things that will surely help me with my Excel spreadsheet.
Anonymous
(Manager)
This was very good, hadn't heard of sumifs before. Helped me to understand vlookups better also
Thomas Tamura
(Consultant at Buxbaum HCS)
Excellent instructor. Entertaining and very informative. Increase in understanding of sumifs.
Sharon Vincent
(CFO at MJC Electronics Corporation)
This course extended my understanding of both functions. Jeff made it fun and interesting.
Susan Parsons
(Controller at ARS DM Select Services)
Love that Jeff is so excited about Excel formulas! He definitely makes this lesson fun.
Jordan Saffron
(Systems and Process Assurance Associate at RSM)
This was an awesome course to learn about the difference between sumifs and vlookups.
Anonymous
(Consultant)
Good overview. I had not use SumIFS in many files but I will start to use them more.
Anonymous
(Controller)
This was the first course I tried and it was very helpful and clear. I like Illumeo!
Anonymous
(Director of Cloud Solutions)
The class was very informative and I would highly suggest my peers take this class.
David Walker
(Consultant at DRWalker Consulting)
Really appreciated the instructor's sense of humor - made a technical subject fun!
Judy Silverman
(SVP, Finance at First Look Media)
Excellent instructor. I especially liked the trick Jeff shared in Application 3.
Bette Shepherd
(CFO at D&S Green Services)
The instructor kept my attention and did a great job of explaining the funcitons
Anonymous
()
Helpful course, always useful getting a refresher on these 2 functions. Thanks!
Anonymous
(Associate Director, CFCC Assurance)
Great class and informative. Learned new things about both Vlookup and Sumifs.
Ravi Patel
(Tax Accountant at Patel)
sumif is a very complex formula but not as vlookup are. loved this self study!
Gary Hall
( at Self employed)
Superior instruction! Great teaching style and easy to follow and understand.
Deborah Duncan
(Accounting Manager at Intermedix)
This was the best explanation of VLOOKUP and SUMIFS that I have ever heard.
Laura Hirschfeld
(CPA at Julia Clark & Associates, PC)
Great information that I will definitely use!!! Fantastic presenter too!
Megan Tiegs
(Consultant at Salo LLC)
Love how excited instructor was! Made it easy to stay completely engaged.
Corey Masters
(Accountant, Internal Auditor, Risk Speci.. at Self)
I really liked the instructors enthusiasm and knowledge of the topic.
Bryan Oliver
(Senior Accounting Analyst at UMB Financial Corporation)
Great course. These are two of the better formulas to use within excel
Anonymous
(Accounting Associate)
This course gave very thorough explanations of the 2 Excel functions.
Regina Ketterer
(Accounting Systems Analyst at Charter Communications)
Gave great ways to understand the components of vlookup and sumifs
Anonymous
(Card Program Administrator)
Very good review of both SumIfs and VLookup. I enjoyed the class.
Anonymous
(CFO)
The instructor made it interesting and was engaged throughout.
Anonymous
(Senior Internal Auditor)
The course provided the required information quite effectively
Jennifer Bor
(Sole Practitioner at Accounting and Business Management Consu..)
good enough for me to get my cpes for my cia and cpa licenses
Omar R. Graham CPA, MBA
(Financial Officer at University of Toronto)
The Instructor is excellent and carries the course with ease.
Tonia Mathew
(Financial Analyst at UT MD Anderson Cancer Center)
I really liked the flow and the material of the presentation.
Samantha Hollibone
(Consultant at DLC Group)
this class was great and I definitely learnt a lot of tips!
Karen Harper
( at JLL)
GREAT OVERVIEW - REFINES WHE TO USE WHAT FOR SPECIFIC NEEDS
Rajeev Sahni
(Director - Capital (RWA) at UBS)
clarity of concept explained followed by practice examples
JANICE CRAIG
(CFO and Corporate Secretary at FireFox Gold Corp)
Well explained and appreciated completing examples twice
Anonymous
(Financial Analyst)
Jeff's enthusiasm kept me engaged for the whole course.
Nicholas O C
(D at NOC)
It was very good, he is very much a likable character
Anonymous
(Accounts Payable Specialist)
I love your enthusiasm about Excel! Very informative.
Anonymous
(APS)
Very good presentation on the argument to use SumIfs.
Anonymous
(Senior Accountant)
This was a good course, comparing the two functions.
Anonymous
(Senior Accountant)
Jeff had go examples to follow and had good energy.
Anonymous
()
Pretty basic course. explained both functions well.
Simon Adell
(Chief Financial Officer at Undisclosed)
Really interesting deep dive on a cool Excel topic.
Anonymous
(Asst Finance Director)
Love this course.
Entertaining and it hits home.
Catherine Choy
(Risk Advisory Supervisor at RSM)
Went over good material and all types of examples.
Sanjeev Ranga
(Financial Analyst at Cisco LIFT - Hired 2017)
A workbook to follow along with would be helpful.
Chantell Haines
(Accountant at SustainableBrands)
The teacher had great energy and enthusiasm!
Anonymous
(Accountant)
Excellent and impressive presenter!
Charles Elwis
(None at none)
Another useful course by Jeff.
brian bleifeld
(Partner at Bleifeld CPA LLC)
good course on hard subject
Ramakrishna Nalluri
(Senior Financial Analyst at Morneau Shepell Inc)
New learning. Good session
Radhesh Pradhan
(Accounting Manager at DGN Technologies Inc)
Amazing course! Loved it.
Jeffrey Miller
(Financial Analyst at LNCE)
Interesting presentation
Anonymous
(Payroll Analyst | Tax & Compliance)
Great course. Thanks!
scott steiner
(attorney cpa at slo, pllc)
Excellent comparison.
Anonymous
(None)
Well presented.
Anonymous
(Staff Accountant)
Great lesson!
Ann Kennedy
(Chief Financial Officer at EsmokerCanada)
Good job Jeff.
Anonymous
(Senior Internal Auditor)
Nice
Prerequisites
Course Complexity: Intermediate
No Advanced Preparation or Prerequisites are needed for this course.
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 .
Great course!
One question = Isn't the last final exam answer incorrect? I thought VLOOKUP would only find the first value, not the sum of all values, so I picked the second answer, but the course says that the first answer was correct.
Question:
If the lookup value is found on multiple rows in the lookup range, what does the function return?
Response:
Should have chosen
Both SUMIFS and VLOOKUP return the sum of all matching rows
Wrong
VLOOKUP returns the related value of the first matching row, and SUMIFS returns the sum of all matching rows
Neal...hmmm...thanks for the heads up. The correct answer is "VLOOKUP returns the related value of the first matching row, and SUMIFS returns the sum of all matching rows." I'll have to investigate the exam in this LMS. Thanks for letting me know!
Thanks
Jeff