Using INDEX and Match Functions together – Alternative to VLOOKUP
Before reading this tutorial, please make sure you understand how to use the INDEX and MATCH function. As a recap, the MATCH function determines the relative position of the value while the INDEX function returns the value of the corresponding cell.
Let’s walk through the sample syntax. In this example, we want to find out the number of wins for Oregon. To accomplish this, we use the INDEX function to select cells C3 through C12. Next, we use the MATCH function to select Oregon from cells B3 through B12. What this does is matches Oregon in Cell B5 and returns the value of “11” in C5.
=INDEX(C3:C12,MATCH("Oregon",B3:B12,0))
Is INDEX and MATCH better than VLOOKUP?
In my opinion, using INDEX and MATCH is a better option than using VLOOKUP. By using INDEX and MATCH, the processing power in Excel is much less. Also, it allows you to lookup values not limited to size. For example, a VLOOKUP function is limited to 255 characters. Finally, using INDEX and MATCH is much more dynamic than VLOOKUP. You can add or remove rows and columns without fear of breaking formulas.
