Instructor for this course
more

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: October 19, 2018

95 Reviews (257 ratings)Reviews

5
Anonymous Author
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!
5
Anonymous Author
Good overview. I had not use SumIFS in many files but I will start to use them more.
5
Anonymous Author
The class was very informative and I would highly suggest my peers take this class.
5
Member's Profile
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!
4
Member's Profile
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.
4
Member's Profile
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.
3
Member's Profile
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.
3
Anonymous Author
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.
5
Member's Profile
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!
4
Member's Profile
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.
5
Member's Profile
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.
5
Member's Profile
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.
4
Member's Profile
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.
5
Anonymous Author
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.
4
Member's Profile
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
5
Member's Profile
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.
5
Member's Profile
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.
5
Member's Profile
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.
5
Anonymous Author
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.
5
Member's Profile
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.
5
Member's Profile
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!
5
Member's Profile
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.
5
Anonymous Author
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.
5
Member's Profile
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.
5
Member's Profile
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.
5
Member's Profile
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!
5
Member's Profile
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.
5
Member's Profile
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.
5
Anonymous Author
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.
5
Member's Profile
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.
5
Anonymous Author
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.
5
Anonymous Author
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.
4
Anonymous Author
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!
4
Member's Profile
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.
5
Member's Profile
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!
4
Anonymous Author
Well taught explanation of these functions. But the examples and supporting materials are missing - adding them would be make this a 5 star course.
5
Member's Profile
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.
5
Member's Profile
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.
5
Member's Profile
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.
5
Member's Profile
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.
5
Member's Profile
Jeff kept the subject matter interesting and informative. I appreciated the review and consistent terminology used throughout.
5
Anonymous Author
I have always been worried about the VLOOKUP function and now I feel very comfortable with it. Thank you for a great class!
5
Anonymous Author
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.
5
Anonymous Author
I learned SUMIFS function is more useful than VLOOKUPS in most cases which I used to use only VLOOKUPS. it's so amazing.
5
Member's Profile
Fun and informative way to present the practical uses for two related functions. Glad he added the way to use together.
5
Anonymous Author
Great presentation. The plain language for remembering functions is just what I needed to get them to stick. Thanks!
5
Member's Profile
Knowing the differences between SUMIFS and VLOOKUP will be very helpful. Each have their strengths and limitations.
5
Member's Profile
The presenter was engaging and enthusiastic.
5
Member's Profile
This was presented very well. I use these functions often and still was able to learn some useful information.
4
Member's Profile
Provides a very good explanation of the difference between the two functions and a unique way to combine them.
5
Member's Profile
Course was presented in an effective manner with the right amount of repetition, pace and context. Well done.
5
Member's Profile
very interesting to see them compared side by side. Makes it alot easier to use correct formula now
5
Member's Profile
This course was so very helpful. the way the instructor explained what the functions do was amazing.
4
Anonymous Author
A informative course and I learnt a few things that will surely help me with my Excel spreadsheet.
5
Member's Profile
Excellent instructor. Entertaining and very informative. Increase in understanding of sumifs.
5
Member's Profile
This course extended my understanding of both functions. Jeff made it fun and interesting.
5
Member's Profile
Love that Jeff is so excited about Excel formulas! He definitely makes this lesson fun.
4
Member's Profile
This was an awesome course to learn about the difference between sumifs and vlookups.
5
Anonymous Author
This was the first course I tried and it was very helpful and clear. I like Illumeo!
5
Member's Profile
Excellent instructor. I especially liked the trick Jeff shared in Application 3.
5
Member's Profile
The instructor kept my attention and did a great job of explaining the funcitons
5
Anonymous Author
Helpful course, always useful getting a refresher on these 2 functions. Thanks!
5
Member's Profile
sumif is a very complex formula but not as vlookup are. loved this self study!
5
Member's Profile
Superior instruction! Great teaching style and easy to follow and understand.
5
Member's Profile
This was the best explanation of VLOOKUP and SUMIFS that I have ever heard.
5
Member's Profile
Love how excited instructor was! Made it easy to stay completely engaged.
5
Member's Profile
I really liked the instructors enthusiasm and knowledge of the topic.
4
Member's Profile
Great course. These are two of the better formulas to use within excel
5
Member's Profile
Gave great ways to understand the components of vlookup and sumifs
4
Anonymous Author
The instructor made it interesting and was engaged throughout.
4
Anonymous Author
The course provided the required information quite effectively
3
Member's Profile
good enough for me to get my cpes for my cia and cpa licenses
5
Member's Profile
The Instructor is excellent and carries the course with ease.
5
Member's Profile
I really liked the flow and the material of the presentation.
5
Member's Profile
this class was great and I definitely learnt a lot of tips!
5
Member's Profile
clarity of concept explained followed by practice examples
5
Member's Profile
Well explained and appreciated completing examples twice
5
Anonymous Author
Jeff's enthusiasm kept me engaged for the whole course.
5
Member's Profile
It was very good, he is very much a likable character
5
Member's Profile
Really interesting deep dive on a cool Excel topic.
4
Member's Profile
Went over good material and all types of examples.
5
Member's Profile
A workbook to follow along with would be helpful.
5
Member's Profile
The teacher had great energy and enthusiasm!
5
Anonymous Author
Excellent and impressive presenter!
5
Member's Profile
Another useful course by Jeff.
5
Member's Profile
good course on hard subject
5
Member's Profile
New learning. Good session
5
Member's Profile
Amazing course! Loved it.
4
Member's Profile
Interesting presentation
5
Anonymous Author
Great course. Thanks!
3
Member's Profile
Excellent comparison.
5
Anonymous Author
Well presented.
5
Anonymous Author
Great lesson!
Member's Profile
Good job Jeff.
5
Anonymous Author
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 .

2 QuestionsCourse Questions and Answers

Member's Profile

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

Member's Profile

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

Course Syllabus
INTRODUCTION AND OVERVIEW
  00:36Introduction to Battle of Two Excel Heavyweights: VLOOKUP vs SUMIFS
  14:51VLOOKUP
  7:58SUMIFS
  11:00Competition
  12:37Competition (Continued)
  2:09Conclusion
CONTINUOUS PLAY
  49.10VLOOKUP vs. SUMIFS: The Battle of Two Excel Heavyweights
SUPPORTING MATERIALS
  PDFVLOOKUP vs. SUMIFS: The Battle of Two Excel Heavyweights Glossary/Index
REVIEW AND TEST
  quizREVIEW QUESTIONS
 examFINAL EXAM