5 Reasons INDEX MATCH Is Better Than VLOOKUP | MergeOS

5 Reasons INDEX MATCH Is Better Than VLOOKUP

Excel experts agree that INDEX MATCH makes VLOOKUP virtually obsolete. In this post, we explore five reasons why you should be using INDEX MATCH.

Whether you're a newcomer to Excel or a seasoned user, you've likely encountered the limitations of VLOOKUP in managing your spreadsheets. Today, we're introducing a more efficient, versatile solution.

What is VLOOKUP?


One function that most Excel users learn early on is VLOOKUP. This has become a favorite of many Excel users because it can be used to look up and retrieve data in a table based on criteria that you can set. The V stands for vertical, meaning it will search vertically in the first column. This contrasts the similar HLOOKUP function. The syntax is as follows:

=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

  • lookup_table is the value you’re searching for in the leftmost column of the table
  • table_array is the range of cells that you want to search
  • col_index_num is the column number in the table (relative to the first column)
  • [range_lookup] is an optional argument that specifies if you’re looking for an exact or close match. This is enabled by default.

What is INDEX MATCH?


The superior alternative to VLOOKUP is INDEX MATCH. While VLOOKUP works fine in most cases, it tends to reveal flaws when you start using it in large and complex sheets. The INDEX MATCH formula is actually two different functions; INDEX and MATCH.

INDEX returns a value in a table based on a set of coordinates for the column and row while MATCH searches for a given value in a range of cells and then returns the relative position in that range as a coordinate. A common way to use the INDEX function is to select one value from an array of cells, based one row:

=INDEX(array, row_num)
  • array is range of cells or an array constant.
  • row_num selects the row in array from which to return a value.

=MATCH(lookup_value, lookup_array, [match_type])
  • lookup_value is the value that you want to match in lookup_array
  • lookup_array is the range of cells being searched.
  • [match_type] is a number, either  -1, 0, or 1. We mostly use 0, which means to match the value exactly. 1 is the default and matches the largest value less than or equal to the lookup_value, whereas -1 matches the smallest value greater than or equal to the lookup_value

You can combine the INDEX and MATCH functions to return a specific value based on coordinates. 

You might find this a little complicated at first, but let's explore why it’s a game-changer for your Excel tasks.

Remember the last time you were crunching a deadline and had to manually adjust your entire set of VLOOKUP formulas just because you added a new column? With INDEX MATCH, those days are behind you. 
 

1. Fewer errors thanks to dynamic column references


One of the biggest issues with using VLOOKUP is that it uses a static column reference. On the other hand, INDEX MATCH uses a dynamic column reference which means it can lead to fewer errors when moving around columns. This is an advantage that becomes more apparent as you create larger and more complex spreadsheets.

2. No array restrictions


VLOOKUP requires you to point to a specific table array. When you need to expand this table and pull values from an additional column, you’ll need to readjust all of your formulas. This can be a pain when you need to modify multiple formulas across a large spreadsheet.

3. Insert columns without ruining formulas


Another standout benefit of INDEX MATCH? Freedom from the tyranny of rigid structures. Add new columns at will, without messing up your lookup results. Say goodbye to tedious formula edits.

  


4. Easier to copy formulas


When working with a large spreadsheet or data set, you’ll typically create several lookup formulas. This requires you to drag and copy your formula, meaning you’ll need to rewrite the formula if you’re using VLOOKUP. This is because VLOOKUP requires a specific column reference. With INDEX MATCH, you can simply drag and copy formulas as needed, saving you a lot of time in tweaking formulas.

5. Faster and less intensive on your computer


Lastly, INDEX MATCH is ultimately faster and less intensive on your computer. It might sound strange in the context of Excel, but large and complex spreadsheets can often result in high CPU usage because of all the background calculations going on. VLOOKUP takes up a lot more processing power since it needs to check the entire array in the formula. INDEX MATCH only uses the lookup column and return column, meaning it has fewer cells to consider. In a small spreadsheet, the difference might be minimal, but you’ll certainly notice the speed benefit with a larger data set.

In short, there’s nothing wrong with using a VLOOKUP match for a very simple sheet. However, if you plan to use Excel for more complicated and detailed spreadsheets in the future, learning to use INDEX MATCH gives you a lot more flexibility. Click here for fully working examples in a downloadable spreadsheet that show you how to use INDEX MATCH.

Are you ready to put an end to countless wasted hours copying and pasting data into Spreadsheets and Word documents? Imagine a world where your reports and documents are automatically generated with ease. That's the power of MergeOS. Don't just take our word for it. Experience the difference for yourself with our 2-week free trial.Try it out for free for 2 week here.
 

More Articles