Excel - VLOOKUP vs INDEX MATCH - Which is better

Question

I know how to apply each technique: INDEX/MATCH vs. VLOOKUP (or HLOOKUP).

Instead of focusing on personal preferences, I'm more interested in the following contrasts between them:

1. Is there something that one method can do that the other cannot?

2. Which one is more efficient in general (or does it depend on the situation)?

3. Are any other advantages/disadvantages to using one method vs. the other

Can someone please help me with this?

Answer 1

Since it is much more adaptable and has the potential to be much more efficient depending on how big the lookup table is, I almost always prefer to use INDEX/MATCH. Only in very simple tables where the column index number is dynamic can I really justify using VLOOKUP, yet even in this situation INDEX/MATCH is equally effective.

I hope this helps you.