Please note that you could get around the static reference issue by using a formula within the VLOOKUP syntax, creating a combination formula such as VLOOKUP MATCH or VLOOKUP HLOOKUP. The next two advantages of using INDEX MATCH also come from the fact that VLOOKUP requires a static column reference input for the values you want to return. When you use INDEX MATCH, no such counting is required. This error is especially prevalent when you have a large table array and need to visually count the number of columns you want to move over. It’s a small difference, but this additional step undoubtedly leads to more errors. With the VLOOKUP syntax, you specify your entire table array, AND THEN you specify a column reference to indicate which column you want to pull data from.

The reason this leads to fewer errors is because when you follow the INDEX MATCH syntax, you click directly on the field containing the value you want to return. When you use INDEX MATCH, the formula allows you to manually choose which column you want to pull from. With VLOOKUP, most people will input a specific, static number to indicate which column they want to return from.

The key difference between INDEX MATCH and VLOOKUP is that VLOOKUP requires a static column reference while INDEX MATCH uses a dynamic column reference. Dynamic Column Reference Leads to Fewer Errors

Once you’ve learned INDEX MATCH and started using the formula consistently, you’ll reduce the number of spreadsheet errors you make, become more efficient in navigating Excel, and significantly improve your ability to create complex Excel models that have a heavy database component. If you don’t know how to use INDEX MATCH, please click here for a detailed tutorial. The purpose of this post is to describe in detail all of the benefits of using INDEX MATCH and convince you that you should use INDEX MATCH exclusively for all of your vertical lookups. One major driver of this problem is that most people still don’t fully understand the benefits of switching from VLOOKUP, and without such an understanding, they are unwilling to invest the time to learn the more complex formula. However, many people still resort to using VLOOKUP because it’s a simpler formula. When deciding between which vertical lookup formula to use, the majority of Excel experts agree that INDEX MATCH is a better formula than VLOOKUP.

Vlookup with drop down mac excel 2013