A practical comparison of these two popular functions

The INDEX and MATCH Excel functions, like VLOOKUP, are used to find something from a dataset. The former involves nesting one formula within the other, while VLOOKUP is a single function that works independently.

Both have clear advantages and support approximation searches. However, how you plan to update the table, your willingness to learn syntax rules, and how you want to search the data should determine which one you use.

These functions are available in Office 365 and are confirmed to work in Excel 2016 and newer.

Overall Findings

  • Takes longer to learn how to use.

  • Unaffected by column changes.

  • Supports large lookup value sizes.

  • Search in either direction.

  • Simple to understand; easy to implement.

  • Doesn't do right-to-left searches.

  • Value size searches cap out at 255 characters.

INDEX-MATCH and VLOOKUP are both useful for distinct reasons. Most people who are dealing with simple tables that require few changes will be just fine with VLOOKUP.

The most significant benefit of VLOOKUP is it's easy to use and remember how to use. Most people probably don't need to return to the syntax rules repeatedly to make it work.

However, when you compare VLOOKUP and INDEX-MATCH, it's pretty clear which one is more advanced and therefore more capable. VLOOKUP can't do everything INDEX-MATCH can, but it does win in the easy-of-use department.

Ease of Use: VLOOKUP Is Simple to Understand

  • Steep learning curve.

  • Three hard-to-remember arguments.

  • Intuitive; easy to grasp quickly.

  • Two simple arguments: TRUE or FALSE.

Nesting functions within each other is no doubt more confusing than using just one. It's easy to miss a parenthesis or comma, and understanding how to fix it can get frustrating fast.

VLOOKUP is easy to understand. Here's one way to express it: Using this value, find whatever is in the same row under this column. Its purpose is clear, it has loads of uses, and you can formulate it rather quickly.

For most people, creating a formula with INDEX-MATCH most likely requires repeated tweaking to get it right without getting an error. You might even need to write them separately and then very carefully join them. There are also three options for the match_type argument, and they don't necessarily make sense intuitively, which adds to the function's complexity.

Dynamic: INDEX-MATCH Adapts to Column Changes

  • Formula stays true even if column count changes.

  • Great for large data sets.

  • Can break formula when columns are added or removed.

  • Could require lots of updating.

VLOOKUP requires a number to reference a column, which is fine and will work forever...until the table changes. When you add or remove a column, this number doesn’t automatically change with it. Thus the value it pulls might no longer hold to what you originally intended.

For example, your formula might pull data from the 2nd column. If you add a new column between 1 and 2, the original column is now in the 3rd position, thus changing the results. If you have lots of formulas that use VLOOKUP, updating each of them to reflect the new column quickly becomes tedious, and forgetting even one will throw off results. The function essentially breaks as column numbers change.

INDEX could run into the same problem since it can also reference the column number, but combining MATCH negates this. There's a clear example of this here: you can add new columns, and the formula updates along with the changes, meaning you could add as many columns as you want, and the formula will still find what it needs.

Lookup Flexibility: VLOOKUP Denies Right-to-Left Searches

  • Search from left to right or right to left.

  • Find values larger than 255 characters.

  • Table must be set up in a very specific way.

  • Might need to rearrange data to make it work.

VLOOKUP has a limitation INDEX-MATCH does not, which is search_value (what you're looking up) must always be in the far left position of lookup_table (the data you're searching through). In other words, it can't look to the left, which limits what you can do.

As an example, consider two columns. The left column has the colors red and blue, and the right one has the words apple and sky. INDEX-MATCH lets you determine apple goes with red by searching for apple and returning red, or vice versa. VLOOKUP can do this only if the search value (apple, in this case) is on the left, limiting how you can look up information.

Another way INDEX-MATCH is more flexible is you don't have to worry about the 255 character limit. VLOOKUP works fine in most cases, but it will display an error if the lookup value exceeds that.

Final Verdict

There isn't a one-size-fits-all approach to using these functions to look up data. What you decide to use should depend on a few factors, including your comfort level in Excel, what your data set looks like, and how you intend to use that data.

VLOOKUP is suitable for simple tables which don't need a lot of changes done to the columns. The syntax is also easy to learn and remember. We recommend it for novice users who need to perform simple lookups.

However, if you plan to make lots of column changes and your data set is large, or there are dozens of formulas that need to query information from the same table, learning how to use INDEX and MATCH together is worth it.

  • Which is faster, INDEX-MATCH or VLOOKUP?

    Technically, INDEX-MATCH is faster. VLOOKUP and INDEX-MATCH are equally matched when it comes to unsorted data. However, if the data is sorted, INDEX-MATCH can be up to 30 percent faster than VLOOKUP.

  • How do I use VLOOKUP in Excel with two spreadsheets?

    To use VLOOKUP in a different workbook other than the one you're currently working in, put the file name in brackets, followed by the sheet name and an exclamation point (!). To copy data from another worksheet, include the sheet name in the table_array argument.

  • How do I use VLOOKUP in Google Sheets?

    The syntax for using VLOOKUP in Google Sheets is the same as how it's used in Excel. You can even use VLOOKUP across different Sheets workbooks using the IMPORTRANGE function.

Was this page helpful?