The Vlookup statement is a very powerful function in Excel that is pretty widely used. The Index Match is a more powerful tool, however it is far, far less frequently used because of it’s complexity.
But why use the Index Match over the Vlookup?
I made a video above explaining the differences and how they are used.
Background for the video:
I will use an example of Fantasy Basketball, however you don’t need to know sports in order to benefit from this.
Let’s say you have copied this data from the your daily fantasy sports website. Fanduel in this case.
So this is your large table where you will get your information from.
Now say this is our table that we want to populate:
We want to pull in Fanduel $ and Position from our first main table.
To get Fanduel $, we can use a VLookup. The advantage to Vlookup is that it is simpler. It looks from left to right and since Fanduel $ is to the right of Player name on the main table, we can use VLookup. Our formula for B2 would be:
We are looking up A2, Rudy Gobert in the main fanduel table and look in column B. When it finds it, it goes over to column 5 and pulls in Fanduel $. The FALSE means it needs to find an exact match.
However to pull in Position, we can not use VLookup. VLookup only looks left to right and since position is to the left of Player name in the main table, we need to use Index Match. Index…Match are just 2 functions nested inside eachother to become a Super Function.
We would use this formula in C2:
Breaking this down, the first part is INDEX(fanduel!A:A
That says we want our results to come from column A in the Fanduel table.
The next part, MATCH(Master!A2,fanduel!B:B,0) tells us that we want to match the item in A2, which is Rudy Gobert in column B in the Fanduel Table. The 0 means that it will tell us its exact location. So this Match part will tell us basically what row number Rudy Gobert is. He is in row 13.
So now Excel will look at the first part, and pull the result from column A and row 13. We see it is a C, so it will pull in that piece.
So that’s the main differences between Vlookup and Index Match. I feel both of these are useful. While Index Match is more powerful, it isn’t needed every time. VLookup can be used for the simple pulls. Overall you should learn and practice both.
To understand this more, I suggest checking out the video I made at the top of this post!
And to learn about the VLookup and INDEX Match even more, I suggest checking out my course, Master Excel Functions. It has 4.5 hours of videos and 114 page ebook explaining Excel functions, including the Vlookup and Index Match. Go to : http://excelbyjoe.com/excel-software-solutions/master-excel-functions/