VLOOKUP vs INDEX-MATCH

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.

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!

 

 

This article was written by Jeff Lenning

12 comments:

  1. Chris Chua
    Reply

    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!

    1. Jeff Lenning Post author
      Reply

      Thanks again Chris 🙂

  2. Jason
    Reply

    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.
    Thanks.

    1. Jeff Lenning Post author
      Reply

      Nice validation with COUNTIFS as well 🙂

  3. Joan
    Reply

    Very informative. Thank you.

    1. Jeff Lenning Post author
      Reply

      Welcome 🙂

  4. Onias Chikwata
    Reply

    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.

    1. Jeff Lenning Post author
      Reply

      Thank you 🙂

  5. Rengin Buchanan
    Reply

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

    1. Jeff Lenning Post author
      Reply

      Thanks…glad you could join me 🙂

  6. Mohammed Ali
    Reply

    Awesome addition to my mediocre knowledge of excel…Thanks

  7. Jeffrey
    Reply

    index mapping is very awesome

Leave a Reply

Your email address will not be published. Required fields are marked *