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:

- SUMIFS matches equivalent values when stored as different data types, whereas VLOOKUP does not
- 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
- SUMIFS returns the sum of all matching values, whereas VLOOKUP returns the first one only
- SUMIFS returns 0 when there are no matching values, where as VLOOKUP returns an error when the 4th argument is FALSE
- 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!**

Get a quick email notice when a new Excel article is available

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!

Thanks again Chris 🙂

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.

Nice validation with COUNTIFS as well 🙂

Very informative. Thank you.

Welcome 🙂

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.

Thank you 🙂

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

Thank you Jeff.

Thanks…glad you could join me 🙂

Awesome addition to my mediocre knowledge of excel…Thanks

index mapping is very awesome

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.

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.

Thanks,

Cole