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