

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?
Contents
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 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:
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.
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 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.
Hold on a second. Was this information helpful so far?
...or on other networks!
Twitter: Follow @professorexcel
Facebook:
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.
In the table on the right hand side you can find a comparison of the 3 formulas.
Comparison of VLOOKUP, SUMIFS and INDEX/MATCH
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 more or get it on Amazon!
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.
'Professor Excel Tools': Add more than 60 amazing features to Excel!
Professor's Error Helper for Excel: Solve any error in Excel.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
Subscribe to our popular Excel newsletter! You get all this:
Your welcome gift: Our big 45 pages keyboard shortcuts package.
Subscribe now! In case the sign-up form above doesn't work, please use this page. Sorry for the inconvenience.
Please use this download link to download the Currency Converter Excel add-in!
Please use this download link to download the comment extraction example: http://bit.ly/Extract_Comment_Example
Comments 7
How to Use the Vlookup Formula in Excel - Professor ExcelProfessor Excel
[…] There are usually other ways instead of using vlookup. Please have a look at our article about when to use vlookup, sumifs or index/match […]
John
I thought index-match-match could do all the functions listed above?
Henrik Schiffner
You are right, index-match can do most functions as shown on the table above. But it can’t return a sum of several cells and can’t be used on several search criteria (at least not without further modifications)
Tina
I have the option of being able to use any of the formula. However, can you advise which one excel processes more easily using less memory or ram. My file is huge, so I’m trying to find alternatives and safe the space without using just values.
Henrik Schiffner
Hi Tina,
In such case I’d recommend either VLOOKUP or INDEX/MATCH. INDEX/MATCH is slightly faster than VLOOKUP but both formulas need half the time for calculation than SUMIFS.
For more information please refer to http://professor-excel.com/performance-excel-study/ , section “Which lookup formula is the fastest?”
Same counts for the file size, please refer to http://professor-excel.com/study-reduce-file-size-excel-workbook/#Advice_6_Use_VLOOKUP_instead_of_SUMIFS_or_INDEX-MATCH .
Best regards,
Henrik
ame
can you help me henrik schiffner?
i have data below…
table1:
==========
ITEM | TYPE
==========
a1 | Cash
a2 | Cash
b1 | AP
c1 | AR
table2
=============
ITEM | AMOUNT
=============
a1 | 100
b1 |-100
a2 | 50
a1 | 40
b1 |-90
c1 | 200
result:
=============
TYPE | AMOUNT
=============
Cash | 190 (sumif?)
AP |-190 (sumif?)
AR | 200 (sumif?)
how do I populate a ‘sumif’ formula in the result table?
thanks & regards
Loïc
hey! what about sumproduct? like
=sumproduct(RangeValue*(range1=condition 1)*(range2=condition 2)*…). it’s a lot more flexible than sumifs isn’t it?