One of the most often used functions when creating an Excel model is consolidating data from different sources. There are 3 major formulas for combining data from different tables or worksheets: VLOOKUP, SUMIFS and INDEX/MATCH. VLOOKUP and SUMIFS are rather popular whereas INDEX/MATCH is usually not that well known. So what is the difference between these 3 formulas and which one should you use?

Overview of the 3 formulas

Let’s take a look at each of the three formulas separately, before we eventually compare them and see, which one of them to use in what case.

VLOOKUP

VLOOKUP is probably the best known formula for getting data from another table. The application is fairly easy, although there are some common mistakes which people tend to make. The VLOOKUP formula has four parts:

• The lookup value
• The range, where you are going to search in
• The number of column, which you want to be returned (start counting from the left column of the search range)
• One optional value, which you can always consider to be “false”

Make sure that you don’t make the most common mistakes as having a too small range (search column as well as return column must be within the search range) and start counting the search column as column number 1.

For more information please check this article.

SUMIFS

Example of VLOOKUP, SUMIFS and INDEX/MATCH

SUMIFS only exists since Excel 2007 and is especially useful, as it can regard several search criteria. Also, this application is quite straightforward:

• The column, which the return value is in
• The column, which your criteria is in
• The lookup value
• Optional: Second criteria column
• Optional: Second lookup value
• …

The primary function of SUMIFS is to sum up values matching your criteria. In our case, we must make sure that each criteria combination only exists once in our table. In the example on the right hand side, we would have exactly this problem: There are 3 VW Golfs in our table, so the return value is the sum of the 3 prices.

Please check this article for more information.

INDEX/MATCH

A combination of the two formulas index and match has one more advantage than the VLOOKUP: It returns the value from any column and not just on the right hand side of the search column. That usually makes it more stable, because the return column stays the same if you insert more columns in-between.

The INDEX formula returns the n-th value from an array of cells. With MATCH, you can search for a value within an array of cells and it’ll return the number of the first occurrence. Please refer to number 3 in above picture for an example of how to use it.

For more information about how to use INDEX/MATCH please refer to this article.

Comparison of VLOOKUP, SUMIFS and INDEX-MATCH

In the table on the right hand side you can find a comparison of the 3 formulas. 

Comparison of VLOOKUP, SUMIFS and INDEX/MATCH

• VLOOKUP and SUMIFS can be applied rather easily, whereas the INDEX/MATCH combination is – at least for beginners – more difficult.
• All of the formulas can return numbers as their return value.
• Unfortunately, SUMIFS can’t get a text as the return value. That means, if you need to get a text returned, SUMIFS is out of the game.
• The return column for the VLOOKUP must be on the right hand side of the search column. Of course, there are workarounds: Changing the order of the columns or duplicating the return column.
• VLOOKUP as well as INDEX/MATCH return the value first in the list. If your search value appears a second time, the second occurrence won’t be regarded. SUMIFS returns the sum. 
• SUMIFS and INDEX/MATCH are rather stable, as they will still work if you change any column order or delete/ insert columns.
• SUMIFS is the only of the three formulas, which can by default regard several search criteria. If you want to search for more than one criteria with one of the other two formulas the best way would be to insert a column with a ‘primary key’. This key combines all the search columns into one column. You then only search within this column. 

---

Excel too slow? Speed it up. Get the book now!

Tired of waiting for Excel? Use the 30 best methods described in this book to speed up Excel calculations!

• Learn how Microsoft Excel performs calculations
• Use the simple and effective step-by-step guide to master each method
• Get to know the impact each method will have on performance

Learn more or get it on Amazon!

---

When to use which formula

Follow this decision tree for identifying which formula to use.

The most important question after knowing all three of the formulas: In what case to use which of them. Therefore just follow the decision tree on the right hand side. 

If your return value is a numeric value (like a number or date), SUMIFS has a some advantages (as shown in the table above) and we recommend using it. If you return value is text, both VLOOKUP and the INDEX-MATCH combination work. The following decisions are then no exclusion criterions but rather matters of convenience.

One more word to the case in which you got more than one search criteria and want to return text: In such case we recommend using a new search column, which combines all the search criterias. For instance, if you are searching for a person by first name and family name and want to return the address, you can concetanate first name and family name into one cell. That way you only have to search for the complete name within one column. 

Bonus: Download summary for free

We’ve prepared a handy printout for you: A summary of the most important information about when to use VLOOKUP, SUMIFS or the INDEX/MATCH combination. Just click on this link (2 MB) and the download starts right away. We hope, it is helpful for you! 

• Name: Printout_VLOOKUP-SUMIFS-INDEX-MATCH.pdf
• Link: https://professor-excel.com/download/Printout_VLOOKUP-SUMIFS-INDEX-MATCH.pdf
• Filetype: PDF
• Size: 2 MB