There are numerous lookup functions in Excel, and often, their capabilities overlap. And, that is true with many things in Excel…there are often multiple ways to accomplish any given task. Performing lookups is no exception. So, how are we supposed to know which lookup function to use? Often, Excel users try to decide between VLOOKUP and the INDEX-MATCH combo. So, which of these two options is better? I was recently asked this question by Chris Chua, who asked the same question of many Excel MVPs.

So, I’ll share my response and then provide the link so you can check out all of the responses from the Excel experts interviewed by Chris.

Note: depending on your version of Excel, you may have XLOOKUP as an option … more info here: https://www.excel-university.com/xlookup/

My Thoughts

When users need to do a lookup task, they often consider traditional lookup functions, such as VLOOKUP and INDEX/MATCH. But, I start by considering SUMIFS first. If the value I’m trying to return is numeric, I’ll use SUMIFS (instead of VLOOKUP or INDEX-MATCH), and here’s why:

SUMIFS makes an awesome lookup function!

Here’s why:

  1. SUMIFS matches equivalent values when stored as different data types, whereas VLOOKUP does not
  2. SUMIFS doesn’t care about the column order, whereas VLOOKUP is designed with the assumption that the lookup column is the first (left-most) column in the range
  3. SUMIFS returns the sum of all matching values, whereas VLOOKUP returns the first one only
  4. SUMIFS returns 0 when there are no matching values, where as VLOOKUP returns an error when the 4th argument is FALSE
  5. SUMIFS doesn’t break when a new column is inserted between the lookup and return columns, but VLOOKUP does

Here is a quick video that demonstrates these differences:

If the value I’m trying to return is a text string, then I’ll probably use INDEX-MATCH for recurring-use workbooks that will be updated each period because the formula will be less likely to break when new worksheet columns are inserted.

Or, if this is a one-time workbook, or the structure is unlikely to be updated, I’ll probably go with VLOOKUP.

So, my thoughts are this: next time you are faced with a lookup task and the return value is numeric, give SUMIFS a try…it makes an amazing lookup function 🙂

All Responses

To view all responses of Chris Chua’s question, please visit his post at this link.

Thanks Chris for putting together this interview and consolidating all of these ideas in one place!

If you have any additional ideas, please share by posting a comment below…thanks!



Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.


  1. Chris Chua on May 25, 2017 at 3:06 pm

    Hey Jeff, thanks for the mention. Great video to demonstrate the use of SUMIFS as an alternative lookup function which many people tend to forget about!

    • Jeff Lenning on May 25, 2017 at 3:07 pm

      Thanks again Chris 🙂

  2. Jason on May 26, 2017 at 6:12 am

    Great point about using SUMIFS.
    I just had a project where I was trying to lookup on multiple criteria and return a number.
    I decided to use SUMIFS, but incorporated a COUNTIFS beforehand to check if there was more than one matching criteria.
    Working good.

    • Jeff Lenning on May 26, 2017 at 6:42 am

      Nice validation with COUNTIFS as well 🙂

  3. Joan on May 26, 2017 at 6:48 am

    Very informative. Thank you.

    • Jeff Lenning on May 26, 2017 at 6:48 am

      Welcome 🙂

  4. Onias Chikwata on June 2, 2017 at 12:42 am

    Thanks Jeff. I like the way you combine a lot formulas in your videos. It makes life very easy for some of us who are daily Excel Users. Be blessed.

    • Jeff Lenning on June 2, 2017 at 9:32 am

      Thank you 🙂

  5. Rengin Buchanan on June 14, 2017 at 8:08 am

    I just took the webinar regarding Lookup and Sumifs formula and I really enjoyed it and learned a lot.
    Thank you Jeff.

    • Jeff Lenning on June 14, 2017 at 8:35 am

      Thanks…glad you could join me 🙂

  6. Mohammed Ali on August 23, 2017 at 12:28 pm

    Awesome addition to my mediocre knowledge of excel…Thanks

  7. Jeffrey on October 4, 2017 at 10:12 pm

    index mapping is very awesome

  8. Tony on January 11, 2018 at 12:07 pm

    I read all of your posts and have really learned a lot, especially from the “hacks”. I would mention that one other good comparison of lists containing names can be done quickly with Conditional Formatting. Highlight one list of names, hold down control and highlight the other. Then format with unique or duplicate values. Select “unique” with the color or your choice. Those names on the left list that are not on the right list will be colored and those on the right list that are not on the left list will also be colored.

  9. Cole on January 14, 2020 at 1:17 pm

    Hi Jeff, Does it have to be a dollar value in the sum criteria in order to use the Sumifs? Like, I am using numbers not the dollar, received errors with Sumifs formula but Vlookup worked. Please advise.

Leave a Comment