Excel tips: Using INDEX & MATCH instead of VLOOKUP

Excel tips: Using INDEX & MATCH instead. For most Excel users,  is a very useful and easy-to-use function. Therefore, this function plays an extremely important role in many Excel spreadsheets. However, VLOOKUP still has some limitations that can cause some troubles for users, especially for those working with large and complex Excel files.

WHAT ARE THE LIMITATIONS OF VLOOKUP?

The three main limitations of that this article wants to address are:

Looking up from the left: searches for data from left to right, meaning that your lookup value must be in a column to the left of your data table.
Inserting or deleting columns: you don’t want to break your by inserting or deleting any columns in your lookup table.
Slow processing speed: using a lot of  functions can slow down your worksheet when you’re working with a large spreadsheet with many lookup formulas.
To address these limitations, users can consider applying the combination of INDEX/MATCH to perform similar functions without facing the above-mentioned issues.

For example, in this sample scenario, I need to find reference information (Column G) from the table below. The MATCH function will help identify the relative position of that value in the table. Meanwhile, INDEX will return a value to search for based on information about the row and column you specify. The INDEX/MATCH function is combined as follows:
= INDEX (column to return value, (MATCH (lookup value, lookup reference column, 0)))

Hopefully, combining the INDEX/MATCH functions will be useful for Excel users, especially for cases where they are having trouble with VLOOKUP.