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?


Is VLOOKUP ever better than INDEX match? ›

INDEX-MATCH is much better: It's never slower than VLOOKUP and can be much faster. It returns a reference rather than a value, which allows us to use it for more purposes. It doesn't care where the result array is with regard to the lookup array.

Does VLOOKUP default to exact or approximate matching If you leave off the last argument? ›

This is the default: if we leave out the optional fourth argument in the brackets VLOOKUP uses Approximate Match mode.

What is the last argument for the match function? ›

The match_type argument specifies how Excel matches lookup_value with values in lookup_array. The default value for this argument is 1.

When using VLOOKUP () An exact match is returned when the final argument is set to? ›

Outcome #1: VLOOKUP Finds Exact Match

If vlookup finds a value that is equal to the lookup value then it returns a result from that same row. This is essentially the same as a normal vlookup where you set the last argument to FALSE. The function finds an exact match and returns that row as a result.

Is VLOOKUP obsolete? ›

XLOOKUP just killed VLOOKUP: everything to know about this major new Excel function. Microsoft recently announced the upcoming release of a new function called XLOOKUP. This function will be replacing the widely used VLOOKUP, HLOOKUP and INDEX/MATCH functions to run searches in a table of Excel data.

Why do people still use VLOOKUP? ›

The VLOOKUP and HLOOKUP functionality is retained so the people who have older spreadsheets can use them on the latest versions of Excel. There are also times when the simplicity of the older functions make them a faster or easier implementation.

What are the common mistakes in VLOOKUP? ›

Most common VLOOKUP mistakes
  • Incorrect data – #N/A error. ...
  • Incorrect function name – #NAME? ...
  • Invalid VLOOKUP cell references – #N/A error. ...
  • Incorrect lookup range – #N/A error. ...
  • Incorrect column number – #VALUE! ...
  • Incorrect Number format – #N/A error. ...
  • Excess spaces and non-printing characters – #N/A error.
Feb 7, 2023

What is last argument in VLOOKUP? ›

The last argument of the VLOOKUP function, known as range_lookup, asks if you would like an approximate or an exact match. In most cases people are looking for a particular product, order, employee or customer and therefore require an exact match.

What are the known limitations of the VLOOKUP function? ›

VLOOKUP disadvantages:

only provides data in a column. a single value must be in the row. data types should be the same in both columns. cannot lookup values from the left.

How do I get the last VLOOKUP match? ›

Vlookup the last matching value in Excel with an easy feature
  1. Click Kutools > Super LOOKUP > LOOKUP from Bottom to Top, see screenshot:
  2. In the LOOKUP from Bottom to Top dialog box, please do the following operations: ...
  3. Then, click OK button, all the last matching items have been returned at once, see screenshot:

What is the INDEX of last argument? ›

Explanation: argc – 1 is the index of the last argument in command line arguments.

How do I use INDEX and match instead of VLOOKUP? ›

By combining the INDEX and MATCH functions, we have a comparable replacement for VLOOKUP. To write the formula combining the two, we use the MATCH function to for the row_num argument. In the example above I used a 4 for the row_num argument for INDEX. We can just replace that with the MATCH formula we wrote.

What are the two main causes of errors for VLOOKUP? ›

#N/A in approximate match VLOOKUP

If your formula looks up the closest match, (range_lookup argument set to TRUE or omitted), the #N/A error can appear in two cases: The lookup value is smaller than the smallest value in the lookup array. The lookup column is not sorted in ascending order.

Why is my VLOOKUP not working when there is a match? ›

If you omit to supply match type in a range_lookup argument of VLOOKUP then by default it searches for approximate match values, if it does not find exact match value. And if table_array is not sorted in ascending order by the first column, then VLOOKUP returns incorrect results.

Why do we place true or false at the end of a VLOOKUP function? ›

Fourth Parameter

A parameter of FALSE means that VLOOKUP is looking for an EXACT match for the value of 10251. A parameter of TRUE means that a "close" match will be returned.

Do accountants use VLOOKUP? ›

VLOOKUP and HLOOKUP are two of the most useful Excel functions for accountants. These functions let you search a table of data and give all the appropriate information for an individual set of data based on only one part of it.

What are the disadvantages of VLOOKUP how do you overcome them? ›

What are the Limitations of the VLOOKUP Function?
  1. VLOOKUP Only Looks to the Right. ...
  2. VLOOKUP Only Finds the First Match. ...
  3. The Default Setting for VLOOKUP is an Approximate Match. ...
  4. VLOOKUP is not Case Sensitive. ...
  5. Inserting a Column will Give Incorrect Results.

Why is XLOOKUP better than VLOOKUP? ›

The XLOOKUP defaults to an exact match where the VLOOKUP defaults to an approximate match. As the exact match is used most often, this setting would make the XLOOKUP more effective. On top of this, the XLOOKUP offers an additional option of an approximate match returning the next larger value.

Is VLOOKUP an advanced Excel skill? ›

The advanced Excel skills to look for in a data analyst are: Pivot tables. VLOOKUP, INDEX, and MATCH functions.

Is there any reason to use VLOOKUP over Xlookup? ›

VLOOKUP defaults to the closest match whereas XLOOKUP defaults to an exact match. To correct that in VLOOKUP, you have to type FALSE as your fourth argument.

Why is VLOOKUP so unreliable? ›

The flexibility of the Function

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

What are the four arguments of VLOOKUP? ›

The Syntax of the VLOOKUP Function

The function accepts four arguments: lookup_value, table_array, col_index_num, and range_lookup. The first three are mandatory arguments, while the last one is optional.

How accurate is VLOOKUP? ›

99.99% of the time, everybody uses FALSE as the range lookup criteria because 99.99% of the time, we need an exact match from the table array. Even in the training sessions, your trainers must have explained only FALSE criteria and would have said not to worry about the TRUE criteria.

How do I return the last match in Excel? ›

Find the Last Occurrence – Using MAX function
  1. The MAX function is used to find the row number of the last matching name. ...
  2. SUMPRODUCT is used to ensure that you don't have to use Control + Shift + Enter, as SUMPRODUCT can handle array formulas.
  3. INDEX function is now used to find the date for the last matching name.

What is difference between VLOOKUP and Xlookup? ›

XLOOKUP vs VLOOKUP – Key Differences

XLOOKUP searches for data both horizontally and vertically. VLOOKUP searches only vertically. XLOOKUP can refer to the left of the lookup_value. It always looks up the values towards the right of the lookup_value.

What is the disadvantage of index match? ›

The main disadvantage of INDEX and MATCH is that it's not well known. Therefore other people working on your workbook might not immediately understand it. Applying the INDEX and MATCH combination is comparatively difficult.

Which of the following are main reasons to use INDEX match instead of VLOOKUP? ›

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

Which of the following is an advantage of INDEX match formula over VLOOKUP? ›

Index Match eliminates a lot of the restrictions that Vlookup does. The look value doesn't have to be in the leftmost table. You can simply use the row number of the value you want and match a value in another column to that row number.

What is the best lookup function in Excel? ›

The most commonly used LOOKUP functions in Excel are VLOOKUP and HLOOKUP. VLOOKUP allows you to search a data range that is set up vertically. HLOOKUP is the exact same function, but looks up data that has been formatted by rows instead of columns.

Which function is best for lookup in Excel? ›

The VLOOKUP function in Excel is a powerful function used to lookup data in a table organized vertically. It looks down the left column of a range to find a value.

Why does VLOOKUP return wrong value? ›

VLOOKUP returning incorrect results

If you omit to supply match type in a range_lookup argument of VLOOKUP then by default it searches for approximate match values, if it does not find exact match value. And if table_array is not sorted in ascending order by the first column, then VLOOKUP returns incorrect results.

Why do people use INDEX match? ›

INDEX MATCH is a clever way to perform a two-way lookup in Excel by combining the power of the INDEX and MATCH functions. It is used as a workaround for the limitations of VLOOKUP, and offers great flexibility once you understand how it works.

What is the disadvantage of INDEX match? ›

The main disadvantage of INDEX and MATCH is that it's not well known. Therefore other people working on your workbook might not immediately understand it. Applying the INDEX and MATCH combination is comparatively difficult.

What are the advantages and disadvantages of indexing? ›

The benefits of index investing include low cost, requires little financial knowledge, convenience, and provides diversification. Disadvantages include the lack of downside protection, no choice in index composition, and it cannot beat the market (by definition).

What are some limitations of VLOOKUP? ›

What are the Limitations of the VLOOKUP Function?
  • VLOOKUP only looks to the right.
  • VLOOKUP only finds the first match.
  • If the optional fourth argument is omitted you may get incorrect results if you want an exact match lookup.
  • Inserting a column anywhere in the lookup table will give incorrect results.

What is the replacement for VLOOKUP? ›

INDEX MATCH MATCH to search in rows and columns

In the above examples, we used INDEX MATCH as the replacement for classic VLOOKUP to return a value from a predefined one-column range.

What is faster than VLOOKUP? ›


With both tables on the same sheet, INDEX MATCH performed much slower than VLOOKUP (8.9 against 6.6 seconds). But if we move the lookup table to another worksheet, the formula starts working much faster (~ 5 seconds), which is better than VLOOKUP.

What is Xlookup good for? ›

Use the XLOOKUP function to find things in a table or range by row. For example, look up the price of an automotive part by the part number, or find an employee name based on their employee ID.

Which data structure is best for lookup? ›

Looking at complexity analysis, Hashtables seem to be the most efficient for lookups.


Top Articles
Latest Posts
Article information

Author: Otha Schamberger

Last Updated: 11/06/2023

Views: 6071

Rating: 4.4 / 5 (75 voted)

Reviews: 82% of readers found this page helpful

Author information

Name: Otha Schamberger

Birthday: 1999-08-15

Address: Suite 490 606 Hammes Ferry, Carterhaven, IL 62290

Phone: +8557035444877

Job: Forward IT Agent

Hobby: Fishing, Flying, Jewelry making, Digital arts, Sand art, Parkour, tabletop games

Introduction: My name is Otha Schamberger, I am a vast, good, healthy, cheerful, energetic, gorgeous, magnificent person who loves writing and wants to share my knowledge and understanding with you.