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.

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!