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

## FAQs

### 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? ›

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.

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? ›

INDEX MATCH formula

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)

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.