Vlookup Vs Index Match The debate ends here (2023)

If you are wondering which performs better in the VLOOKUP vs INDEX/MATCH comparison, this article will try to provide you with an answer. During one of my Excel training sessions, I was surprised to find that most people knew and in some cases were proficient in using VLOOKUP, but almost none of them knew about the powerful INDEX/MATCH combination. And when I mentioned it, they had a lot of questions:

Is it a search and reference formula? - Yeah! Perhaps the best of all. Is it a new feature in Excel 2016? - No chance! Can you reference data in different worksheets or workbooks? – Yes Is it better than VLOOKUP? - It depends

I think the VLOOKUPPR team is doing a much better job than INDEX/MATCH. In this tutorial I will try to compare these two formulas as objectively as possible. Initially I don't have favorites, but I prefer to use the INDEX/MATCH combination than VLOOKUP. The choice depends on many factors, including what the data will look like and how it will be used. I will cover all of that in this article.

VLOOKUP Vs INDEX MATCH - An objective comparison

So, let's begin and end this eternal VLOOKUP vs. VLOOKUP INDEX/MATCH and which one is better. And to do that, I'm going to compare these two functions based on some parameters (some are quantifiable and some are not).

The popularity of the resource.

VLOOKUP takes this without hesitation. For some people, if you know how to use VLOOKUP, you know how to use Excel. And considering how much can be done with that single feature, I don't blame them. That's why many people use VLOOKUP because other Excel users understand it better. While this isn't a popularity contest, it does play a big part in why VLOOKUP is used so often. Imagine you are working with a client/manager who knows about VLOOKUP but doesn't know how to use INDEX/MATCH. It makes more sense to use a function that you both know than to try to teach them INDEX/MATCH.

Easy to use

The reason why VLOOKUP is so popular is that it is easy to use (at least compared to INDEX/MATCH). When taking an Excel training course, I would always tackle VLOOKUP first. Many people find VLOOKUP difficult, so I can't even imagine teaching them INDEX/MATCH (unless they already know how to use VLOOKUP proficiently). And in most cases, VLOOKUP is good enough. You can do most of the things that people need when working with data. Of course you can do the same with INDEX/MATCH too, but why do it the hard way when you don't even have to? VLOOKUP takes 3 arguments (fourth is optional) and is easier to understand compared to INDEX/MATCH, which are separate functions, each with three arguments and must be combined to perform a search (still getting complicated?). While you may find INDEX/MATCH easy to use once you get the hang of it, a beginner would probably gravitate towards VLOOKUP.

The flexibility of the function.

VLOOKUP is a great feature, but it has some limitations:

You cannot search and return a value to the left of the search value. It only works with data arranged vertically. VLOOKUP would give an incorrect result when adding/removing a new column in your data (since the column number value now refers to the wrong column). You can make the column number dynamic, but if you plan to combine two or more functions, why not use INDEX/MATCH first?

The INDEX-MATCH combination, on the other hand, is designed to solve all of these problems. If someone can use a join with formulas to make VLOOKUP look to the left or make columns dynamic, I'm sure that person would be better off using INDEX/MATCH, which is designed to do these things with ease. So yes, VLOOKUP can get around these limitations, but it's not worth it. Of course, with more coding and robotics, you could also get VLOOKUP to get your favorite coffee, but keep in mind that it wasn't designed for that.

formula speed

With small data sets, the difference is almost imperceptible. However, with thousands of rows and many columns, this can be crucial. Without reinventing the wheel, here is a post by Charley Kyd where he clearly mentions: While I considered this as one of the factors, I think it is less important than others. Over the years, Microsoft has worked hard to improve the speed of these features and has made significant improvements since I first wrote this article. Here's an update where they mention how to make formulas like VLOOKUP, HLOOKUP, and MATCH faster. Also, it's a very small percentage of people who can actually benefit from the speed improvement that comes with using INDEX/MATCH instead of VLOOKUP.

Veredicto general (VsLOOKUP Vs INDEX/MATCH Combo)

While I'm a big fan of INDEX/MATCH, to be fair I have to admit that VLOOKUP is still king. This doesn't mean you shouldn't learn about INDEX/MATCH, but if you're new to Excel and looking for formulas, start with VLOOKUP. Use and master first, then switch to INDEX/MATCH INDEX/MATCH is a powerful combination, more flexible and faster than the VLOOKUP formula.

That said, VLOOKUP isn't going anywhere and will likely remain one of Excel's most popular functions for a long time to come. Well, the debate is not over yet (see the comments section). Feed the fire: leave your 2 cents in the comments section.

The difference between VLOOKUP and INDEX/MATCH

Before we get into the comparison, I think it's important to understand the fundamental difference (and more importantly, the similarities) between these two features. I won't go into the syntax and example of these functions. If you're interested, I have detailed tutorials on VLOOKUP and INDEX/MATCH. Both VLOOKUP and INDEX/MATCH are formulas you can use to look up a value in a record and get the corresponding value (just like you do a menu and look up the price of the dish you want to order).

In most cases, you can use them interchangeably. The major differences in these features become more important when you need more than just a simple search and retrieve operation. Here are some key differences between these two formulas (discussed in more detail below). 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 Features:

Free Online Excel Training (7 Part Video Course) 100+ Excel Functions (with Examples and Videos) 20 Advanced Excel Functions and Formulas (For Excel Professionals)

John Verghese If you have structured references in Excel sheets, INDEX()/MATCH() works/looks better because you can see what kind of data is being searched for: =INDEX(tbl_Items[Price],MATCH([ItemNr], tbl_Items[ArticleNr ],0)) Find the Price field in the table tbl_Items =VLOOKUP([ItemNr],tbl_Items,8,FALSE) Find the eighth column in the table tbl_Items. For lists with many columns, it can be difficult to get the correct column index for VLOOKUP(). End of discussion! Actually! 🙂 Links:http://blog.contextures.com/archives/2016/05/05/enter-complex-excel-formulas-fast/The complex formula In the comment, Wyn showed the formula that puts in AutoCorrect. It is an INDEX/MATCH formula, with placeholders for cell references. (You can read more about this powerhouse duo on my website.) If you want to try this tip (and I highly recommend it), copy his formula. I'll show you what to do with that in a moment. =INDEX(DblClk_to_Select_Column_to_return, MATCH( DblClk_Single_Lookup_Cell, DblClk_Lookup_Column, 0),0) Also, I think there is room to exhaust this discussion, ie H. Personally, I think MS could add a query to the conflict between VLOOKUP and INDEX MATCH. Many people don't appreciate the simplicity and control you have with MS Query to perform complex queries along with other data transformations in a single SQL query instead of developing complex formulas with errors or array formulas. I'm waiting for your opinion. Also read my perspective on revising VLOOKUP with other alternatives in terms of performance.http://www.analystcave.com/excel-vlookup-vs-index-match-vs-sql-performance/Fixed perceived VLOOKUP issues/weaknesses! End of story. This is Arikrishnan. So it's a pleasure to get in touch with you. I need a favor from you regarding a tracker you updated to compliance (http://trumpexcel.com/2015/03/excel-leave-tracker/). In this tracker, he has made the fields "Out this month (cell NJ)", "Out this year (cell NK)" up to cell NQ constant and only the cells assigned to each month change. My requirement is that I need the above fields to be modified as I want to use them for the monthly report. Can you help me with this!!!! I used VLOOKUP and INDEX/MATCH and still use both. If a client comes to me and complains that a solution broke because of VLOOKUP, we can get it back. But, seriously. INDEX/MATCH people are like religious people knocking on your door looking for a problem that fits your solution. BOOOOO! The reason I think INDEX is strong is that since it returns a reference, it can be used in conjunction with the SUM function ( SUM(INDEX() ) to sum ranges.Another useful function is to select values ​​from different sections (something like as a scene manager) if you use it in a reference mode using the [area_num] parameter at the end.http://www.decisionmodels.com/optspeede.htm http://www.excelguru.ca/forums/showthread.php?132-INDEX-MATCH-versus-VLOOKUP&p=599&viewfull=1#post599Generally:http://mrexcel.com/articles/excel-vlookup-index-match.php http://exceluser.com/formulas/excels-vlookup-vs-index-match-functions.htmSeveral:http://www.excelhero.com/blog/2011/03/the-imposing-index.htmlWarning, Zack=VLOOKUP(“z”,CHOOSE({1,2},$B$1:$B$10,$A$1:$A$10),2,FALSE) You say that... VLOOKUP would return an incorrect result when you add/remove a new column in your data. But this is true of so many things in Excel that a good spreadsheet developer could code this =VLOOKUP(“g”,$D$2:$H$15,MATCH(“Qtr2”,$D$2:$H$2, 0 ) , FALSE) VLOOKUP can/is as flexible as INDEX/MATCH. The biggest selling point for me is that VLOOKUP is and remains easy to teach people, INDEX/MATCH less so, and while I know all about INDEX/MATCH, my first resource is usually VLOOKUP; I usually only use INDEX/MATCH when there's a twist in the requirement where I'm putting a conditional test on the search. I developed an event/employee scheduling system using LOTUS 123 in the late 90's which I converted to Excel sometime in 2004. Since then the system has evolved a lot and been populated with many automated features and will continue to evolve. I once considered incorporating any of the above methods, but found that my own personal coding was more efficient for the task at hand. The reason I'm asking about the size of your tables is because my tables rarely reach more than 250 records. The total project size has been reduced over the years from around 1,048,000 to 78,000 with the same number of data sets. This was achieved by removing all formulas and conditional formatting from all worksheets. Everything is done in VBA code. Note: I don't use pivot tables because the result is not to my liking and it doesn't serve our needs if it affects the use of the above functions. By the way, the assessment that INDEX ( MATCH()) can work with horizontally and vertically aligned data and VLOOKUP() is not true, but it ignores HLOOKUP(). Using HLOOKUP() instead of VLOOKUP() is as simple as switching from column references to row references in INDEX(MATCH()). I won't bore you with all the other nonsense I have to say about this, but here's a summary of what I do with VLOOKUP(): left-right-search-return; Search by multiple criteria; Bulletproof with named columns and tables; Returns the first/last criteria. In short, one of the best things about VLOOKUP() is that I can walk users through it step by step. Typically, I develop a workbook and review it with the owner, usually an executive or director. This may be the first time you have walked through the VLOOKUP() functions. You mostly get it. However, when I start with INDEX(MATCH()), your eyes light up at the first explanation. After wrapping them in VLOOKUP(), I can add the necessary complexity for larger problems. If you want to read the rest it's onhttp://bigdon-in-vbaland.blogspot.com/2014/11/indexmatch-v-vlookup.html.Thanks, I would always start a MATCH/INDEX match with a helper cell for MATCH. This is the point at which the returned row index can be visually inspected. In many cases it will be incorrect, so there is no point in searching for more data fields until the data and formula issues are fixed. Once the row index is validated, INDEX returns as many fields as needed (including a copy of the lookup field for validation as in both VLOOKUP strategies). I find this a much more satisfying position than the usual cry for help: "Why is my VLOOKUP returning the wrong value? It's something I probably won't use. In terms of speed, VLOOKUP and INDEX(MATCH()) are equally slow. If you really care about speed, you'd switch to Charles Williams' concept of using two VLOOKUP(,,,TRUE) instead of one INDEX(MATCH()) and you'd see a 100-fold speed increase.For flexibility, he overlooked the fact that MATCH(,, -1) might return a little more than just less than just less in a range lookup. Also, if you need to create 12 columns of VLOOKUP, a single MATCH column with 12 INDEX columns is faster. But the ease of use and popularity trumps everything here people can get into VLOOKUP most of whom are brand new to INDEX and MATCH unless someone absolutely needs to have VLOOKUP on the left hand or VLOOKUP for a slightly higher value. e greater , people will still use VLOOKUP 99% of the time. Specifying TRUE for a close match when the lookup column is not sorted will cause the VLOOKUP function to return incorrect values ​​(unless it's just luck). So in the example above, if the PartNumber column is not sorted, you'll get wrong results. It doesn't matter how fast it is if it's not right. What am I missing? I explain binary search in this video:https://youtu.be/GllJpJxOSvMIs there still a difference in workload between vlookup and index-match in the new Office Excel 2013 suites?