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: May 18, 2018
74 Reviews (211 ratings)Reviews
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.
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.
Michael Kelly
( 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.
Rahman Ullah
(General Ledger Accountant at Lloyds Register)
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!
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.
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
Dominick Pariano
(Senior Auditor at PolyOne)
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.
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.
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.
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
(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!
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.
Jeffrey Bone
(Lead Financial Analyst at CenturyLink)
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.
Erika Phillips
(Controller at Fallbrook Technologies)
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.
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.
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
()
I learned SUMIFS function is more useful than VLOOKUPS in most cases which I used to use only VLOOKUPS. it's so amazing.
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.
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.
Sam Gephart
(Controller at Element Materials Technology)
This was presented very well. I use these functions often and still was able to learn some useful information.
Ted Feher
(CFO at Turbo Drill Industires Inc)
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.
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
()
A informative course and I learnt a few things that will surely help me with my Excel spreadsheet.
Thomas Tamura
(Consultant at Buxbaum HCS)
Excellent instructor. Entertaining and very informative. Increase in understanding of sumifs.
Sharon Vincent
(VP of Finance at MJC Electronics Corporation)
This course extended my understanding of both functions. Jeff made it fun and interesting.
Susan Parsons
(NA Controller at Learning Tree International USA, Inc.)
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
(Controller)
This was the first course I tried and it was very helpful and clear. I like Illumeo!
Bette Shepherd
(CFO at D&S Green Services)
The instructor kept my attention and did a great job of explaining the funcitons
Judy Silverman
(Finance & Strategic Planning Consultant at Self-Employed)
Excellent instructor. I especially liked the trick Jeff shared in Application 3.
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.
Megan Tiegs
(Consultant at Salo LLC)
Love how excited instructor was! Made it easy to stay completely engaged.
Bryan Oliver
(Senior Accounting Analyst at UMB Financial Corporation)
Great course. These are two of the better formulas to use within excel
Regina Ketterer
(Accounting Systems Analyst at Charter Communications)
Gave great ways to understand the components of vlookup and sumifs
Anonymous
(CFO)
The instructor made it interesting and was engaged throughout.
Omar R. Graham CPA, MBA
(Financial Officer at University of Toronto)
The Instructor is excellent and carries the course with ease.
Jennifer Bor
(Sole Practitioner at Accounting and Business Management Consu..)
good enough for me to get my cpes for my cia and cpa licenses
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!
Rajeev Sahni
(Director - Capital (RWA) at UBS)
clarity of concept explained followed by practice examples
JANICE CRAIG
(CFO, VP Finance at in transition)
Well explained and appreciated completing examples twice
Simon Adell
(Chief Financial Officer at Undisclosed)
Really interesting deep dive on a cool Excel topic.
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 Sustainable Brands)
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.
Ann Kennedy
(Chief Financial Officer at EsmokerCanada)
Good job Jeff.
Anonymous
(Staff Accountant)
Great lesson!
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