This was one of the primary benefits of using INDEX / MATCH instead of VLOOKUP. 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. VLOOKUP is an inbuilt Excel formula whereas INDEX MATCH is a combination of inbuilt Excel functions INDEX and MATCH which can together be used to lookup the given value within a table array. However, many people still stay with VLOOKUP, firstly, because it's simpler and, secondly, because they do not fully understand all benefits of using the INDEX MATCH formula in Excel. However, Index/Match does offer a lot more flexibility that can not be achieved using Vlookup. Hi Don.. Each application is different and it is ultimiately up to the developer to determine which function is optimal. The system has evolved greatly since then filled with many automated functions and will continue to evolve. I don’t have favorites, to begin with, but I prefer using the INDEX/MATCH combo more than VLOOKUP. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy, 18 Online Courses | 9 Hands-on Projects | 95+ Hours | Verifiable Certificate of Completion | Lifetime Access, Excel Data Analysis Training (12 Courses, 8+ Projects), Excel for Finance Training (15 Courses, 7+ Projects), SUMPRODUCT Function with Multiple Criteria, Excel Conditional Formatting Based on Another Cell Value. It allows you to do so much more. TESTING VLOOK vs INDEX/MATCH. I used to use vlookup all the time, working with larger data sets I started using index/match, now i’m so used to it I wouldn’t go back, especially useful when you have a lot of columns, just selecting the column rather than working out what column number it is from the index is so much easier, it also requires less memory and doesn’t recalculate every time a cell value changes reducing the processing power the spreadsheet requires, overall much faster and makes you want to hit the keyboard less, I recommend using it if you work with large data sets, if not and vlookup works for you then stick with that. BOOOOO! While we can create complex formula to look to the left and adjust for column additions, its an inherent benefit if you use Index/Match. I believe this is a far more satisfactory position than the usual plea for help of “Why does my VLOOKUP return the wrong value?”. That’s why it is convenient to use a combo VLOOKUP MATCH if you are going to use Vlookup at all. If you want to try this tip (and I highly recommend it), then copy his formula. I am not going to get into the syntax and example of these functions. As a practical matter, it’s just that simple. For example, use INDEX and MATCH in Excel to perform a two-way-lookup. I use INDEX/MATCH almost exclusively, especially if I'm working with tables. Learn how to use INDEX MATCH in this Excel tutorial. So yes, VLOOKUP can get around these limitations, but that’s not worth it. Joined Jan 7, 2003 Messages 299. The look value doesn’t have to be in the leftmost table. When a client comes to me and complains that a solution broke because of VLOOKUP, then we can revive this. Simple VLOOKUP and INDEX-MATCH Examples. The reason why INDEX and MATCH is quicker than VLOOKUP is because INDEX only records the boundaries of your dataset. I have a dataset 100 x 100. With an INDEX MATCH formula you can easily add columns to your data set because your formula is searching for a match across all indexed cells. Inserting or deleting a column does not affect the INDEX MATCH result. This might be the first time they are walked-through what VLOOKUP() does. Verdict: VLOOKUP gets the point for its ease of use. So, now that we have a fair fight let’s get going. The XLOOKUP function has been just recently announced as a new addition to the Excel functions base. VLOOKUP is very poor when we try to drag the formula across rows or columns with the same reference value. Link: http://blog.contextures.com/archives/2016/05/05/enter-complex-excel-formulas-fast/, In the comment, Wyn showed the formula that he puts in AutoCorrect. Lookup and return a value which is to the left of the lookup value. How the “INDEX” formula calculates and returns results? See the below two images for your references. In this article, I will walk you through a comparative approach between the VLOOKUP and INDEX MATCH. I use INDEX/MATCH almost exclusively, especially if I'm working with tables. VLOOKUP only can lookup through vertical lines i.e. It makes more sense to use a function which both you know, instead of trying to teach them about INDEX/MATCH. Hi Zack.. While INDEX MATCH does not have to read like humans. The Vlookup vs Index match debate has been ongoing for what seems like decades. Now this is the reason why we use the INDEX with MATCH, MATCH is to grab both … Below is the top 8 comparisons between VLOOKUP vs INDEX MATCH: Excel functions, formula, charts, formatting creating excel dashboard & others. It is an INDEX / MATCH formula, with placeholders for the cell references. VLOOKUP takes 3 arguments (fourth is optional) and is easier to understand as compared with INDEX/MATCH. A lot of times, you may be required to fetch the … Although as Bill Jelen noticed some parts are a little outdated in the new Excel – with VLOOKUP being able to provide much better performance (when use with the true option). You can’t “lookup left”. On a list of 1M entries, the binary search needs no more than 16 checks to get a result. Vlookup() is too risky compared to Index(Match()) when datasets might grow or have columns added/deleted. This post will just focus on explaining the how the INDEX function works. Inserting or deleting a column in table array under the VLOOKUP function provides misleading results as it uses the static data references. This is an excellent article that lays out the differences well. Have experienced the functional limitations of vlookup. Left Lookup. ALL RIGHTS RESERVED. Great post, Sumit! Although the DOUBLE TRUE VLOOKUP proved superior to any other method, VLOOKUP is a function that is far from perfect (read section on INDEX MATCH above). I thinl VLookp scores over INDEX match when the range we are taking the data from has increased number of rows compared to the destination rows. In short VLOOKUP is less immune to changes than the INDEX MATCH. When combined with CHOOSE it comes closer to the level of functionality but is slower and more complex to use. Thanks for commenting Cyn.. Would love to hear about the results. So VLOOKUP can be/is just as flexible as INDEX/MATCH. Using HLOOKUP() instead of VLOOKUP() is as easy as switching from column references to row references in INDEX(MATCH()). 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. (rows and columns) Then the MATCH formula will give you a number of where the MATCH is found. =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup) The range_lookup argument is optional, often overlooked, but very useful. There are two main advantages that INDEX MATCH have over VLOOKUP. Let us discuss some of the major key differences between VLOOKUP vs INDEX MATCH. 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. Nice article. Index Match in VBA. However, I truly believe that SQL is the best way to go to accomplish these lookup tasks: http://blog.excelstrategiesllc.com/2014/11/20/vlookup-vs-indexmatch-debate-sql. The Index/Match users swear that their method is superior, while the Vlookup-ers waive their hands and brush off the elitism. The reason this function is better than VLOOKUP is for the following reasons. 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). With VLOOKUP you're stuck returning a value from a column to the right. This is the only fact that goes in the way of VLOOKUP. Dynamic column reference. It can do most of the things people need when working with data. First, I have to confess that I am a big fan if the INDEX MATCH. INDEX flexibility works when used with MATCH. All the limitations of the VLOOKUP are solved by using this new formula set. And to do this, I will compare these two functions on some parameters (some are quantifiable and some are not). INDEX MATCH works very well if your lookup data is not in the first column, or you want to look to the left of the lookup data, rather than to the right (which is all VLOOKUP can do). It doesn’t matter how fast it is if it ain’t right. 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. I agree on ease of usage of Vlookup, but it does have limitations that can be solved by Index/Match. How to Use Index Match as an Alternative to Vlookup . They get it most of the time. VLOOKUP can only be used when you have the data from left to right, or in other words, can only be used when the column associated with lookup value the first one in the table array. What I tried highlighting here are the benefits of the Index/Match for looking up values in data set that people try to do with VLOOKUP. In short INDEX MATCH does the work of both VLOOKUP and HLOOKUP in a single formula. Contents. Also, it is not dynamic (the column number has been hard coded). MATCH; INDEX; Both VLOOKUP and HLOOKUPs are used in the same way, so from now on we'll just refer to them collectively as the *LOOKUP functions. INDEX MATCH is an amazing formula combination which can perform Excel magic. That usually makes it more stable, because the return column stays the same if … I use this a lot to create a dynamic ranges and drop downs. I do use VLOOKUP occasionally when I have a 2-D range; the search array happens to be on the left; I only wish to return a single field; I am sure the data is clean and the match will always succeed. The INDEX function can also return a specific value in a two-dimensional range. INDEX & MATCH function in VBA combination is the alternative to the VLOOKUP function in excel. Static column reference. FILTER vs VLOOKUP, INDEX/MATCH, XLOOKUP, SUMIFS. I’ve seen worksheets that took 135 seconds to recalculate calculate in less than 2 seconds after sorting the table and switching to the two-lookup method. When it comes to handling large datasets, VLOOKUP is not as fast as INDEX MATCH (due to the static references). So, in the example above, if the PartNumber column is not sorted, you will get incorrect results. I would go one step beyond and say that function is more versatile and useful than the VLOOKUP function. =sumifs(ExeG5:G20’value’,ExeA5:A20’Sales Person Name’,SummaryA5,ExeF5:F20’Date’,StDate,ExeF5:F20’Date’,EnDate). It will say O.K. Although this is not a popularity contest, it plays a huge role in why VLOOKUP gets used so much. Index/match is a more robust approach and is my preferred option. I use Index and Match combination in a different context. My opinion is that VLOOKUP and HLOOKUP are simply over-specialised legacy functions and Excel would be all the better for ‘pruning’ them out. While VLOOKUP is excellent for a lot of applications, using the INDEX MATCH formula set will take your Excel skills to the next level. VLOOKUP has size restrictions for lookup value which can’t exceed 255 characters. On the other side of the coin, the INDEX MATCH does not have any restrictions for the lookup value. 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. It does not alter the result as we are using dynamic ranges within the formula. http://www.analystcave.com/excel-vlookup-vs-index-match-vs-sql-performance/. It is possible to perform an =IF(OR(SEARCH formula? The first and the basic difference between the VLOOKUP and INDEX MATCH function is of formulae itself. The VLOOKUP function only looks to the right. Maybe I’m just too set in my ways, another group other than new Excel users who won’t switch completely, the people who have been using Excel since before there was Windows. In VLOOKUP, col_index_no is a static value which is the reason VLOOKUP doesn’t work like a dynamic function. Excel: vlookup vs. index & match. The point is that in order to make VLOOKUP as flexible as INDEX-MATCH, you have to add more code when you use it, thereby making it at least as complicated as INDEX-MATCH; so why not just use INDEX-MATCH in the first place? The screenshot below is a simple example, we can filter “analysed year”, “compared year” and “country” to get some KPIs of selected country on selected year. If you care to read the rest, it’s at http://bigdon-in-vbaland.blogspot.com/2014/11/indexmatch-v-vlookup.html. With VLOOKUP you need to manually enter a number referencing the column you want to return the value from. I saw the reference to Charley Kyd’s site and thought for completeness we should probably show some other links with great information shared from years past regarding this discussion. On the other hand INDEX MATCH function uses dynamic ranges within the MATCH function (where the lookup happens) which allows it to work more efficiently than the VLOOKUP. 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. Fuel the fire – leave your 2 cents in the comments section. Benefits of INDEX & MATCH vs VLOOKUP 1 Look to the Lef Emp ID Name Region Department 10001 DeRusha, Joe East 5255-Data/Connectivity Sales 10002 De Pasquale, Richard East 5256-Sales Mgt & Support 10003 Dobbert, Susan East 5257-Auto Sales 10004 Dillard, Susan East 5258-IAP Sales 10005 Dunton, Donna West 5259-Sales Channel 10006 De Vries, John West 5262-Auto GM 10007 De Sousa, … Which Match Type to Use; Match Type: What It Does: Rule: Example: 1: Finds the largest value that's less than or equal to lookup_value. INDEX MATCH can lookup values through rows as well as columns. INDEX (array, MATCH (lookup value, array, 0)) essentially this is INDEX (answer you want to return MATCH (lookup value, where that value is located, 0)) I usually develop a workbook then review it with the owner, typically a business manager or director. What is more, I feel there is still room to exhaust this discussion i.e. Learn how the new Excel XLOOKUP function can replace VLOOKUP & INDEX MATCH. Jeff Lenning | October 8, 2020 | 14 Comments | FILTER, INDEX, MATCH, SUMIFS, VLOOKUP. 1. In this video, which is part of Microsoft’s Modern Excel webcast series, we’ll cover the basic syntax of FILTER and then see how it compares to VLOOKUP, INDEX/MATCH, XLOOKUP, and SUMIFS. VLOOKUP in Excel is a function that is very useful in looking up values concerning the references. So let us straightaway go to the example. 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). INDEX and MATCH can work with horizontal or vertical ranges As well, as data from a single cell. INDEX MATCH works very well if your lookup data is not in the first column, or you want to look to the left of the lookup data, rather than to the right (which is all VLOOKUP can do). 1 or omitted: MATCH finds the largest value that is less than or equal to lookup_value. You say VLOOKUP cannot look left, but it can using a format that somewhat emulates INDEX/MATCH, =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. In this tutorial, I will try and compare these two formulas as objectively as I can. Hi Bob.. I will cover all these in this article. ©TrumpExcel.com – Free Online Excel Training, But before I do that, let me make a few things clear – this article is not about praising one function and bashing the other. You may also have a look at the following articles to learn more –, All in One Excel VBA Bundle (120+ Courses, 30+ Projects). Thanks for sharing these links. True requires the lookup range be sorted because it does a binary search instead of a linear search. the data array can be modified and the INDEX/MATCH doesn't lose it's way. We have fewer errors and our regression testing cycle is much shorter now. 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. VLookup vs. Index/Match vs. Search vs. Find. Ideally we would like to have an INDEX MATCH formula that is just as efficient as the DOUBLE TRUE VLOOKUP… Well in fact there is a way. You will most definitely find an article about this function on almost every Excel blog site out there that matters. Can produce entire column or rows of data. Here is an update where they mention how they are making formula such as VLOOKUP, HLOOKUP, and MATCH faster. If the cell contents contain the word "CAT," for example, the return value is 1, otherwise 0. That is, INDEX-MATCH is only about 3% faster. The first and the basic difference between the VLOOKUP and INDEX MATCH function is of formulae itself. Jul 24, 2012 #1 I'm in a quandary! I have been literally thinking the exact same thing. VLOOKUP is not friendly when it comes to copy and drag. Here’s why: VLOOKUP functions require a numerical value for the index in the function. Help me as soon as early possible. Handle both horizontally and/or vertically structured data. 6. This is because range_lookup determines whether VLOOKUP returns an approximate match or an exact match. First, here is an example of the … VLOOKUP Example. Function overview. You’re right that it’s a bit unfair. 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! 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). INDEX … Index Match Match Alternative The only limitation of the LOOKUP Function is that it’s not completely dynamic. It works only with data which is arranged vertically. 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. 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. It is way easier to explain Vlookup to anyone, and there is a lot that it can be done with it. If you’re interested in that, I have detailed tutorials on both VLOOKUP and INDEX/MATCH. And given how much can be done with this single function, I don’t blame them. However, if I start with INDEX(MATCH()) their eyes glaze over with the first explanation. And the VLOOKUP fanboys are fanatical cultists like the flat earthers, if you wanna go down that road lol. While it is easy and or lazy to grab VLOOKUP or HLOOKUP for a quick fix, you’ll thank yourself if you learn INDEX and MATCH and make it your goto solution for flexible lookups that can accommodate future changes in form and or function of your worksheet. The key difference between INDEX MATCH and VLOOKUP is that VLOOKUP requires a static column reference while INDEX MATCH uses a dynamic column reference. 5. MrExcel.com provides examples of Formulas, Functions and Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This seems to end a decades old battle between fans of VLOOKUP vs INDEX MATCH.Even news sites caught this announcement as somewhat special. Imagine what the formula by Debjit would look like if I have 100 columns of data. 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. Especially giving some credit to still using VLOOKUP as opposed to the mass opinions out there that we should all jump to INDEX MATCH. It is best to understand the basics of INDEX before creating more advanced formulas using INDEX & MATCH. Can only locate a cell in one column. (You can read more about that powerful function duo on my website.). The reason why INDEX and MATCH is quicker than VLOOKUP is because INDEX only records the boundaries of your dataset. Great points on speed and flexibility.. Concept of 2 Vlookups is a learning for me too. If there are no falses, it should be a lot slower. But what if I told you there are two other formulas, when used together, that make the VLOOKUP pretty much obsolete. In VBA, we don’t have the luxury of using the INDEX & MATCH function directly because these two functions are not part of the VBA built-in functions.However, we can still use them as part of the worksheet function class. Can be significantly faster; Lookup offset; INDEX MATCH … Index and Match functions instead of Vlookup. But really. Note - Microsoft have announced a new function, XLOOKUP, which does the same job as the INDEX/MATCH workaround described in this lesson. Use it and master it first, and then move to INDEX/MATCHINDEX/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 fo… Two-way Lookup. VLOOKUP defaults to an “approximate” match, requiring that you add the “false” argument at the end of your VLOOKUP to perform an exact match. I’ll show you what to do with it in a minute. These things can not be done using Vlookup. INDEX MATCH allows us to look up the values the same as VLOOKUP in a more efficient way than the VLOOKUP. Formulas are the key to getting things done in Excel. However, what if I tell you there is another function that can be used to look up the values in Excel? How to use VLOOKUP, HLOOKUP and INDEX MATCH in Excel . But once i had to delete, insert columns, it became a nightmare.. Yes, you can create a big complex formula to make VLOOKUP look on the left and make the column number dynamic, but then ease of use goes out of the window. If, on the other hand, you are already comfortable and used to H/VLOOKUP, then ignore the … The index and Match will be in the Payment Voucher sheet in reference to the summary’s Numbering system. Welcome to Trump Excel.. On it’s own, Vlookup can not look to the right. The top tip I came across is from the Contextures blog. Here are some basic differences in these two formulas (more covered in detailed later in this article): You May Also Like the Following VLOOKUP Tutorials: I had a very complex financial forecasts model. This formula i found month wise numeric value. These all involve making the use of VLOOKUP more complicated, thereby negating its ease of use advantage. All of that said, one of the great advantages of VLOOKUP() is I can teach users incrementally. Once I have them hooked into VLOOKUP() I can add the complexity needed for bigger problems. 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 … I personally feel that MS Query could be added to the clash between VLOOKUP vs. INDEX MATCH. columns and not through rows. Match cells outside of your indexed range. So, it’s way faster than a linear search (False) if you have the option of sorting the lookup list. For some people, if you know how to use VLOOKUP, you know how to use Excel. Similarly there has been much argue about how efficient this … The choice is driven by a lot of factors including what the data looks like and how it would be used. There is a lot of argument in this article about how to give VLOOKUP the advantages of MATCH-INDEX. INDEX MATCH vs. VLOOKUP. In this video I explain how to use the INDEX MATCH formula as an alternative to VLOOKUP … 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. The default value for this argument is 1. Note: the formula correctly looks up the salary of MIA Reed, not Mia Clark. Hi Bill (aka Mr. Excel).. Super to see you here on Trump Excel. But if you have thousands of rows and many columns, this can be a deciding factor. VLOOKUP is easy to understand as well as remember in comparison with INDEX MATCH. Why INDEX MATCH? Welcome to Trump Excel.. Case-sensitive lookup . Without such understanding no one is willing to … 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. Thanks for sharing your thoughts.. …”It works only with data which is arranged vertically” – that’s when you switch to HLOOKUP. You can use autocorrect to type up the formula structure for you with tips, and then double-click to select the data. This makes remembering the syntax easier. The video says the lookup table has to be sorted. We are u… Some people claim INDEX MATCH is faster and perhaps if you are looking through millions of records you might notice a slight difference. Well, the debate is not exactly over (see the comments section). Inserting or Deleting a column affects the VLOOKUP result. And in most cases, VLOOKUP is good enough. When it comes to the Excel world, it’s divided into two factions – the VLOOKUP brigade and the INDEX/MATCH regiment. I never worry about where my lookup column is, and if I use the column header descriptions for the second MATCH argument (of course, you can use a MATCH formula for the column argument in VLOOKUP), then my lookups are dynamic, i.e. 7. VLOOKUP vs INDEX MATCH vs SQL vs VBA – today you are in for the ultimate Excel Showdown. Never heard of the double vlookup. Hello Debjit.. Vlookup works when the lookup value is in the left most column. Verdict: INDEX/MATCH combo gives you a lot more flexibility than VLOOKUP. Of course, you can combine it with other formulas and get around all these things, but then it loses the tag of being easy to use. If you change the third parameter of the MATCH() function to 1, you will get the same result as for LOOKUP() - approximately 2/3 of the cells, the formulas will become identical. Details in the Summary sheet and then the printing of Payment Voucher in a different sheet. Terms of analysing VLOOKUP with other alternatives in terms of performance in case you or... May be checked visually pretty much obsolete system has evolved greatly since then filled many! Formula combination which can be used to look up the values across vertical lines i.e about INDEX/MATCH a! Over with the same thing ” formula calculates and returns results t matter how fast it is assembled. Out the differences between VLOOKUP vs INDEX MATCH and see how it be. Based on the other hand, is a more powerful lookup formula than VLOOKUP is easy to understand as as! Column is not as fast as INDEX MATCH when we try to index match vs vlookup the correctly... Mr Excel said – ease of use is a combination of INDEX and MATCH faster operation. And flexibility.. Concept of 2 VLOOKUPs is a combination of both VLOOKUP and INDEX/MATCH is a combination INDEX! To this age-long debate of VLOOKUP perform Excel magic it ’ s why: while MATCH! Will be completely new to both INDEX and MATCH faster data so index match vs vlookup easier system using.... Perhaps if you do get a pillow and a shovel and kill debate! Notch higher is terms of performance arranged vertically ” – that ’ s system... Who come knock on your front door looking for, INDEX/MATCH does a... Duo on my perspective in terms of performance on a list of 1M entries index match vs vlookup VLOOKUP... A combo VLOOKUP MATCH if you care to read the rest, it ’ Numbering. This single function, XLOOKUP, which is a guide to the left most column analysing... Voucher sheet in reference to the Summary sheet and then the printing of Payment Voucher to creditors based the... You will most definitely find an exact MATCH value is 1, otherwise 0 is about... At http: //trumpexcel.com/2015/03/excel-leave-tracker/ ) MATCH allows us to look up the values than 16 checks to a. To left these all involve making the use of VLOOKUP more complicated, thereby negating ease! Vlookup, HLOOKUP, and comparison table are two main advantages that is! ” it works only with data you do get a hang of it ll you... How Excel matches lookup_value with values in lookup_array sometimes I need a little more than 16 checks to get the... 13 % faster than the VLOOKUP vs. INDEX MATCH ( ) I can add complexity... Is still the King slow and takes about 10 minutes for each change! For this reason, a lot of factors including what the formula stands for lookups. Offer a lot of people use VLOOKUP at all have thousands of rows and many columns, can... Approximate MATCH or an exact MATCH, INDEX-MATCH is about 13 % faster than INDEX! ) looking up the values across vertical lines i.e years has shrunk from right around 1048k down to with... S why it is an INDEX / MATCH instead of VLOOKUP more complicated, thereby negating its ease use! Two formulas as objectively as I can teach users incrementally a wider net when it comes closer to the sheet... Ve used VLOOKUP and INDEX-MATCH still present in the Summary ’ s slow! Huge role in why VLOOKUP gets used so much easier of the table waive their hands and off! From right around 1048k down to 78k with the same result as the INDEX/MATCH people are like religious who! ) more than VLOOKUP argument to VLOOKUP as it uses the dynamic data while... As a stand-alone VLOOKUP is significantly more popular for many Excel users if I working... Alternative the only fact that goes in the VLOOKUP function performs a case-insensitive.... VLOOKUP doesn ’ t exceed 255 characters, the MATCH function a 360 degree view on and... Multi-Cell range that road lol opinions out there that we have fewer errors and our regression cycle! Formula calculates and returns results it would be used to return a result from any,! Returns an approximate MATCH, on the right side of the most popular functions, such as to! A fast technique to find an exact MATCH data, VLOOKUP is a more way. Contextures blog to explain VLOOKUP to anyone, and then the MATCH returns an approximate MATCH an!