VLOOKUP Vs. INDEX/MATCH – The Debate Ends Here!

During one of my Excel Training sessions, I was surprised to find out that the people were aware and quite proficient in using VLOOKUP, but most of them had no idea about INDEX/MATCH.

Here are some of the queries they had about Index/Match formulas:

  • 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? (we will find out soon, keep reading)

I think the VLOOKUP PR team is doing a much better job than that of INDEX/MATCH.

Here in this blog post, I will try and compare these 2 formulas as objectively as I can (Disclaimer: I do have a penchant for the INDEX/MATCH combo).

Popularity

VLOOKUP takes this hands down. For some people, Excel formulas are synonymous with VLOOKUP. For this reason, a lot of people use VLOOKUP as it is better understood by other Excel users.

Verdict: VLOOKUP is a clear winner on popularity

Ease of Use

VLOOKUP takes 3 arguments (fourth is optional) and is easier to understand as compared with INDEX/MATCH. INDEX & MATCH are 2 separate functions that take 3 arguments each and should be combined to do a lookup. While you may find INDEX/MATCH equally easy when you get a hang of it, a beginner is likely to gravitate towards VLOOKUP.

Vlookup Vs Index

Verdict: VLOOKUP gets the point for it ease of use.

Flexibility

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 can address all these issues. It can lookup and return a value which is to the left of the lookup value, and it can handle both horizontally and/or vertically structured data.

Vlookup Lookup Value on Right

Also, since we are using MATCH to find the position of a column, it does not matter if you add or delete a column. The MATCH part of the formula would automatically take care of the position.

Verdict: INDEX/MATCH combo gives you a lot more flexibility than VLOOKUP.

Speed

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:

“At its worst, the INDEX-MATCH method is about as fast as VLOOKUP; at its best, it’s much faster.”

That says it all!

Verdict: If speed is what you are looking for, INDEX/MATCH combo is the way to go.

Overall Verdict (Vlookup Vs Index/Match)

INDEX/MATCH is a powerful combo that has more flexibility and speed than the VLOOKUP formula. If you haven’t already adopted INDEX/MATCH as the way of life, it is about time you do it.

Vlokup Vs Index Match Comparison

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.

You May Also Like the Following VLOOKUP Tutorials:

Useful Excel Resources:

  • Bill Jelen

    This is an excellent article that lays out the differences well.

    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.

    • Hi Bill (aka Mr. Excel).. Super to see you here on Trump Excel. Great points on speed and flexibility.. Concept of 2 Vlookups is a learning for me too. Will check it out. Thanks for sharing 🙂

      • Bill Jelen

        I demo Charles William’s formula here: http://youtu.be/Guj__8KEQD8?t=4m48s

        • Jan

          Both brilliant articles. Never heard of the double vlookup. What puzzles me now is why it is not assembled in to one excel function with vlookup syntax to get the best of both?

        • Dying_in_this_Crap_World

          This only works if there are false right? If there are no falses, it should be a lot slower.

    • peda

      Quick Question:

      Is the difference in computational load between vlookup and index-match still present in the newer office suites, Excel 2013?

  • Nice article, but I’m still not convinced I should use INDEX(MATCH()) more than VLOOKUP(). 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. Oh, it’s another tool in the kit, but over the years I’ve built too many skills around VLOOKUP() to abandon it.

    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

    • Hi Don.. Welcome to Trump Excel.. Thanks for sharing your thoughts.. I am with you on this.. The ease of use is a huge factor for Vlookup. It is way easier to explain Vlookup to anyone, and there is a lot that it can be done with it. Index/Match, on the other hand, is a notch higher is terms of complexity and can be used in a specific scenario. However, if you do get a hang of it. Index/Match combo is very powerful and can do a lot more than Vlookup. But at the end of the day – as Mr Excel said – ease of use and popularity trumps everything else.

    • peter bartholomew

      Sorry Don, it must be the way you teach it. My opinion is that VLOOKUP and HLOOKUP are simply over-specialised legacy functions and Excel would be all the better for ‘pruning’ them out. 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. Despite that, I think the value they bring to the bloated zoo of Excel functions is not worth their keep.

      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.

    • Wes

      I know this is a year old now, but we just got around to establishing Index(Match()) as best practise and, in fact, banished vlookup() from any spreadsheet deliverables generated by my group. Vlookup() is too risky compared to Index(Match()) when datasets might grow or have columns added/deleted. We have fewer errors and our regression testing cycle is much shorter now.

  • Delgore

    May I inquire into how large are the average data tables stored in Excel when you use VLOOKUP, HLOOKUP or INDEX(MATCH)?

    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.

  • Bob Phillips

    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. 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.

    • Hi Bob.. Welcome to Trump Excel.. Thanks for sharing your thoughts.. I agree with about Vlookup being easy to use and being popular. However, Index/Match does offer a lot more flexibility that can not be achieved using Vlookup. 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. Apart from these, you can also use Index/Match to return entire row/column, as well as a cell reference (which is useful if you create dynamic named ranges or drop downs). These things can not be done using Vlookup. I agree on ease of usage of Vlookup, but it does have limitations that can be solved by Index/Match.

  • Zack Barresse

    Nice article Sumit! 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.

    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

    • Hi Zack.. Welcome to Trump Excel.. Thanks for sharing these links. Really helpful in getting a 360 degree view on Vlookup and Index.

  • jorgeluis500

    Nice article. First, I have to confess that I am a big fan if the INDEX MATCH. However, regarding the third point in the Flexibility section, it’s not completely fair to compare VLOOKUP with a combo INDEX MATCH. If you use INDEX and hard code the numbers for rows and columns, you are going to get a different value if the column is deleted too. That’s why it is convenient to use a combo VLOOKUP MATCH if you are going to use Vlookup at all.

    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.

    • I am with you on this. INDEX flexibility works when used with MATCH. If row/column number are hard-coded, flexibility goes for a toss. And the feature of Index to return reference is incredible. It allows you to do so much more. I use this a lot to create a dynamic ranges and drop downs.

  • ExcelStrategiesLLC

    Great post, Sumit! I agree with majority of your commentators, given the choice, I would take the VLOOKUP route. 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

  • Debjit Chakraborty

    VLOOKUP work also from right to left.

    • Hello Debjit.. Vlookup works when the lookup value is in the left most column.

      • Debjit

        Here my answer.

        • On it’s own, Vlookup can not look to the right. In the above case, you have created a long formula that can be simplified using Index/Match. Also, it is not dynamic (the column number has been hard coded). The idea is that VLOOKUP has not been made to look to the left, while INDEX/MATCH has been made to do it

          • Col Delane

            Sumit: Your comparison of an INDEX/MATCH combo with a stand-alone VLOOKUP is as unfair as comparing apples and oranges! Both Debjit and Bob Phillips have shown how VLOOKUP can be used in combination with another function to return a value from the left. Others have shown how another function can be used to return a dynamic col_index argument to VLOOKUP.

            Perceived problems/weaknesses of VLOOKUP dispelled! End of story.

          • Thanks for commenting.. VLOOKUP doesn’t offer the flexibility that INDEX/MATCH has. Imagine what the formula by Debjit would look like if I have 100 columns of data. 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. In such a case, better use INDEX/MATCH.

          • Md. Golam Mostafa

            hi, Sumit Bansal,
            I want a formula of excel.
            =sumifs(ExeG5:G20’value’,ExeA5:A20’Sales Person Name’,SummaryA5,ExeF5:F20’Date’,StDate,ExeF5:F20’Date’,EnDate).
            This formula i found month wise numeric value. but i want to found month wise text value.
            what is the formula to do this.
            Please it’s so much important for mine. Help me as soon as early possible.

          • Adam

            I have been literally thinking the exact same thing. I know it’s a bit old now, but it makes no sense to compare a single formula with a combination formula in this context!

          • Thanks for pitching in Adam. You’re right that it’s a bit unfair. 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. No doubt VLOOKUP is an amazing function, but Index/Match cast a wider net when it comes to looking up values.

          • Arikrishnan Ece

            Hi Sumit,

            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 !!!!

  • AnalystCave

    Definitely a good analysis of the VLOOKUP vs. INDEX MATCH debate. Especially giving some credit to still using VLOOKUP as opposed to the mass opinions out there that we should all jump to INDEX MATCH. 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).

    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/

  • Cyn

    I have a very large data set that are currently built with vlookup. It’s very slow and takes about 10 minutes for each small change. I want to switch it to index match and see how it goes. I will keep you posted.

    • Thanks for commenting Cyn.. Would love to hear about the results

  • Paul

    INDEX/MATCH is definitely more intimidating than VLOOKUP.
    The top tip I came across is from the Contextures blog. 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.

    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)

  • Reynold

    I use Index and Match combination in a different context. Forms or Payment Voucher and Summary sheet. Details in the Summary sheet and then the printing of Payment Voucher in a different sheet. The index and Match will be in the Payment Voucher sheet in reference to the summary’s Numbering system. Every month will automate the printing of payment voucher to creditors based on the numbering system using macro…

  • XLarium

    Two points in favor of INDEX()/MATCH():

    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!
    🙂

  • John

    Lovely article.
    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.
    Eg: If i want to derive the values for an item say “Orange” on my new sheet which may be on row 81,
    and in the old sheet where it is looking from : “Orange” is on row no 78( i.e the new sheet has increased rows) the formula will return the value pertaining to data on row no 78 instead of actually looking up details for “orange” per se.
    Is there a solution for this in INDEX MATCH so that irrespective of the row number it returns values pertaining to the concerned item eg JOHN??

    John Verghese

  • Aesop Howard

    …”It works only with data which is arranged vertically” – that’s when you switch to HLOOKUP.