If you’re wondering which one scores higher in the VLOOKUP vs INDEX/MATCH battle, this article will try and answer it. During one of my Excel Training sessions, I was surprised to find out that most of the people were aware and, in some cases, proficient in using VLOOKUP, but almost none of them knew about the powerful INDEX/MATCH combo. And when I mentioned it, they had a lot of queries:
Is it a lookup and reference formula? – Yes! Maybe the best of the lot Is it a new Excel 2016 function? – Hell NO! Can it reference data between different worksheets or workbooks? – Yes Is it better than VLOOKUP? – It depends
I think the VLOOKUP PR team is doing a much better job than that of INDEX/MATCH. In this tutorial, I will try and compare these two formulas as objectively as I can. I don’t have favorites, to begin with, but I prefer using the INDEX/MATCH combo more than VLOOKUP. The choice is driven by a lot of factors including what the data looks like and how it would be used. I will cover all these in this article.
VLOOKUP Vs INDEX MATCH – An Objective Comparison
So let’s get started and put an end to this age-long debate of VLOOKUP vs INDEX/MATCH and which one is better. And to do this, I will compare these two functions on some parameters (some are quantifiable and some are not).
The popularity of the function
VLOOKUP takes this hands down. For some people, if you know how to use VLOOKUP, you know how to use Excel. And given how much can be done with this single function, I don’t blame them. For this reason, a lot of people use VLOOKUP as it is better understood by other Excel users. Although this is not a popularity contest, it plays a huge role in why VLOOKUP gets used so much. Imagine you’re working with a client/manager who is proficient in using VLOOKUP but doesn’t know how to use INDEX/MATCH. It makes more sense to use a function which both you know, instead of trying to teach them about INDEX/MATCH.
Ease of USE
The reason VLOOKUP is so popular is that it’s easy to use (at least when compared with INDEX/MATCH). When I take Excel training, I would always start by first covering VLOOKUP first. A lot of people find VLOOKUP hard, so I can’t even imagine trying to teach them INDEX/MATCH (unless they already know how to use VLOOKUP proficiently). And in most cases, VLOOKUP is good enough. It can do most of the things people need when working with data. Surely you can do the same thing with INDEX/MATCH too, but why take the hard road when it’s not even needed. VLOOKUP takes 3 arguments (fourth is optional) and is easier to understand as compared with INDEX/MATCH. INDEX & MATCH are two separate functions that take three arguments each and should be combined to do a lookup (getting complicated already??). While you may find INDEX/MATCH equally easy when you get a hang of it, a beginner is likely to gravitate towards VLOOKUP.
The flexibility of the Function
VLOOKUP is a great function but has a few limitations:
It can not lookup and return a value which is to the left of the lookup value. It works only with data which is arranged vertically. VLOOKUP would give a wrong result if you add/delete a new column in your data (as the column number value now refers to the wrong column). You can make the column number dynamic, but if you planning to combine two or more functions, why not use INDEX/MATCH in the first place.
INDEX-MATCH combo, on the other hand, is made to handle all these issues. It can If someone can use a combination to formulas to make VLOOKUP look to the left or make columns dynamic, I am sure that person is better off using INDEX/MATCH, which is made to handle these things with ease. So yes, VLOOKUP can get around these limitations, but that’s not worth it. With more coding and robotics, I am sure you can also make VLOOKUP fetch your favorite coffee, but remember, it’s not made for this.
Speed of the formula
The difference is hardly noticeable when you have small data sets. But if you have thousands of rows and many columns, this can be a deciding factor. Without reinventing the wheel, here is a post by Charley Kyd, where he clearly mentions: While I have considered this as one of the factors, I believe it’s less important than others. Over the years, Microsoft has been hard at work trying to improve the speed of these functions, and they have made considerable improvements since I wrote this article first. Here is an update where they mention how they are making formula such as VLOOKUP, HLOOKUP, and MATCH faster. Also, it’s a very small percentage of the number of people who can actually benefit from the speed improvement that comes with using INDEX/MATCH over VLOOKUP.
Overall Verdict (VLOOKUP Vs INDEX/MATCH Combo)
Although I am a huge fan of INDEX/MATCH, in all fairness I must admit, VLOOKUP is still the King. This doesn’t mean that you should not learn about INDEX/MATCH, but if you’re new to Excel and lookup formulas, start with VLOOKUP. Use it and master it first, and then move to INDEX/MATCH INDEX/MATCH is a powerful combo that has more flexibility and speed than the VLOOKUP formula.
That being said, VLOOKUP is not going anywhere and is likely to remain as one of the most popular functions in Excel for ages to come. Well, the debate is not exactly over (see the comments section). Fuel the fire – leave your 2 cents in the comments section.
The Difference Between VLOOKUP and INDEX/MATCH
Before getting to the comparison, I think it’s important to know the basic difference (and more importantly the similarities) between these two functions. I am not going to get into the syntax and example of these functions. If you’re interested in that, I have detailed tutorials on both VLOOKUP and INDEX/MATCH. Both VLOOKUP and INDEX/MATCH are formulas you can use to look up a value in a dataset and fetch the corresponding value (just like you scan a menu and look for the price of the dish you want to order).
In most cases, you can use these interchangeably. The main differences in these functions become more important when you need a little more than a simple lookup and fetch operation. Here are some basic differences in these two formulas (more covered in detailed later in this article):
How to Use VLOOKUP with Multiple Criteria. How to make VLOOKUP Case Sensitive. Use IFERROR with VLOOKUP to Get Rid of #N/A Errors. Use VLookup to Get the Last Number in a List in Excel. Excel Index Match Formula vs Function in Excel – What’s the Difference?
Useful Excel Resources:
Free Online Excel Training (7-part Video Course) 100+ Excel Functions (with examples and videos) 20 Advanced Excel Functions and Formulas (for Excel Pros)
John Verghese When you have structured references in Excel tables INDEX()/MATCH() works/looks better because you can see what kind of data is being looked up: =INDEX(tbl_Articles[Price],MATCH([ArticleNr],tbl_Articles[ArticleNr],0)) Looking up field “Price” in table “tbl_Articles”. =VLOOKUP([ArticleNr],tbl_Articles,8,FALSCH) Looking up the eighth column in table “tbl_Articles”. In lists with a lot of columns it might be difficult to get the correct column index for VLOOKUP(). End of debate! Really! 🙂 Link: http://blog.contextures.com/archives/2016/05/05/enter-complex-excel-formulas-fast/ The Complex Formula In the comment, Wyn showed the formula that he puts in AutoCorrect. It is an INDEX / MATCH formula, with placeholders for the cell references. (You can read more about that powerful function duo on my website.) If you want to try this tip (and I highly recommend it), then copy his formula. I’ll show you what to do with it in a minute. =INDEX( DblClk_to_Select_Column_to_return, MATCH( DblClk_Single_Lookup_Cell, DblClk_Lookup_Column, 0),0) What is more, I feel there is still room to exhaust this discussion i.e. I personally feel that MS Query could be added to the clash between VLOOKUP vs. INDEX MATCH. Many people don’t appreciate the simplicity and control you have with an MS Query to do complex lookup operations together with other data transformation in a single SQL query as opposed to developing complex erroneous formulas or Array Formulas. Looking forward to your opinion. Also feel free to read on my perspective in terms of analysing VLOOKUP with other alternatives in terms of performance. http://www.analystcave.com/excel-vlookup-vs-index-match-vs-sql-performance/ Perceived problems/weaknesses of VLOOKUP dispelled! End of story. This is Arikrishnan. So pleasure to get in touch with you. I need a favour from you regarding a Tracker you updated for Attendance (http://trumpexcel.com/2015/03/excel-leave-tracker/). In this Tracker you have made the fields “Leave this Month (Cell NJ)”, “Leaves This Year (Cell NK)” till Cell NQ as constant and only cells allocated for every month changes. My requirement is that, I need those aforementioned fields needs to be changed as I would like to use those fields for monthly report. Can you assist me in this !!!! I’ve used VLOOKUP and INDEX/MATCH and continue to use both. When a client comes to me and complains that a solution broke because of VLOOKUP, then we can revive this. But really. The INDEX/MATCH people are like religious people who come knock on your front door looking for a problem to fit their solution. BOOOOO! Why I find powerful about INDEX is that since it returns a reference it can be used in conjunction with the SUM function ( SUM(INDEX() ) to sum ranges. Another convenient feature is to choose values from different sections (sort of scenario manager) when you use it in a reference mode, using the [area_num] parameter at the end. Speed: http://www.decisionmodels.com/optspeede.htm http://www.excelguru.ca/forums/showthread.php?132-INDEX-MATCH-versus-VLOOKUP&p=599&viewfull=1#post599 General: http://mrexcel.com/articles/excel-vlookup-index-match.php http://exceluser.com/formulas/excels-vlookup-vs-index-match-functions.htm Misc: http://www.excelhero.com/blog/2011/03/the-imposing-index.html Take care, Zack =VLOOKUP(“z”,CHOOSE({1,2},$B$1:$B$10,$A$1:$A$10),2,FALSE) You say that … Vlookup would give a wrong result if you add/delete a new column in your data. But that is true of so many things in Excel, so a good spreadsheet developer can easily code around that =VLOOKUP(“g”,$D$2:$H$15,MATCH(“Qtr2”,$D$2:$H$2,0),FALSE) So VLOOKUP can be/is just as flexible as INDEX/MATCH. The biggest selling point to me is that VLOOKUP is easy to teach to people, and it sticks, INDEX/MATCH less so, and even though I know all about INDEX/MATCH my first recourse is usually to VLOOKUP; I generally only use INDEX/MATCH when there is some twist in the requirement where I am adding some conditional test into the lookup. I developed an event/employee scheduling system back in the late 90’s using LOTUS 123 which I converted over to Excel somewhere around 2004. The system has evolved greatly since then filled with many automated functions and will continue to evolve. I once thought about incorporating one of the above methods but found my own personal coding to be more efficient for the task at hand. The reason I ask about the size of your tables is that my tables rarely reach over 250 records. Total project size over the years has shrunk from right around 1048k down to 78k with the same amount of records. This was accomplished by removing all formulas and conditional formatting from all the sheets. Everything is done in VBA code. Note: I don’t use pivot tables since the output is not to my liking and useless to our needs in case this has any affect on the use of the above functions. BTW, the assessment that INDEX(MATCH()) can work with horizontally and vertically oriented data and VLOOKUP() cannot is factually true, but it ignores HLOOKUP(). Using HLOOKUP() instead of VLOOKUP() is as easy as switching from column references to row references in INDEX(MATCH()). I won’t bore you here with all of the other nonsense I have to say about this, but here’s a summary of things I do with VLOOKUP(): Left-right lookup-return; Multiple criteria lookup; Bullet-proofing with Named columns and tables; Returning first/last of a criteria. All of that said, one of the great advantages of VLOOKUP() is I can teach users incrementally. I usually develop a workbook then review it with the owner, typically a business manager or director. This might be the first time they are walked-through what VLOOKUP() does. They get it most of the time. However, if I start with INDEX(MATCH()) their eyes glaze over with the first explanation. Once I have them hooked into VLOOKUP() I can add the complexity needed for bigger problems. If you care to read the rest, it’s at http://bigdon-in-vbaland.blogspot.com/2014/11/indexmatch-v-vlookup.html. Thanks I would always start a MATCH/INDEX combination with a helper cell for the MATCH. That is the point at which the row index it returns may be checked visually. In many instances it will be wrong so there is no point in moving to look up further data fields until data and formula issues are corrected. Once the row index is validated then INDEX will return as many fields as are needed (including a copy of the search field for checking as in the two VLOOKUP strategy). I believe this is a far more satisfactory position than the usual plea for help of “Why does my VLOOKUP return the wrong value?” Counter to this, I believe a real strength of VLOOKUP is that it works looking up data from closed workbooks but that is not something I am likely to use. On the speed issue, VLOOKUP and INDEX(MATCH()) will be equally slow. If you really cared about speed, you would switch to the Charles Williams concept of using two VLOOKUP(,,,TRUE) instead of one INDEX(MATCH()) where you would see a 100-fold increase in speed. On the flexibility issue, you missed that MATCH(,,-1) can return the value “just larger” instead of the value “just smaller” when doing a range lookup. Also, if you have to do 12 columns of VLOOKUPs, then a single MATCH column with 12 INDEX columns will be faster. But ease of use and popularity here trumps everything else. People can wrap their heads around VLOOKUP, where most will be completely new to both INDEX and MATCH. Unless someone has a specific need to VLOOKUP left or VLOOKUP for the value just larger, people will continue to use VLOOKUP 99% of the time. Specifying TRUE for Approximate Match when the lookup column is not sorted causes the VLOOKUP function to return incorrect values (unless it just gets lucky). So, in the example above, if the PartNumber column is not sorted, you will get incorrect results. It doesn’t matter how fast it is if it ain’t right. What am I missing? I explain the binary search in this video: https://youtu.be/GllJpJxOSvM Is the difference in computational load between vlookup and index-match still present in the newer office suites, Excel 2013?
