Say Goodbye to VLOOKUP
The most searched Excel formula on Google and YouTube is VLOOKUP. It makes sense. It can do a complex task in a millisecond.
Why say goodbye?
It's just for a short while. Until you master INDEX & MATCH. Up to that point, don't write another VLOOKUP. Sure it'll take you longer, but once you get a good grip on it, the next time you come across a complex lookup problem, you know what to do. Once you're comfortable with INDEX, feel free to go back to VLOOKUP for the simpler lookup problems.
My recipe: Simple lookups = VLOOKUP; complex lookups = INDEX (or SUMPRODUCT or other array combinations)
3 reasons why Excel experts replace VLOOKUP with INDEX & Match
- Unlike VLOOKUP, which searches only to the right, INDEX & MATCH can look in both directions – left and right.
- INDEX & MATCH is less prone to errors. Assume you have a VLOOKUP where the final value you want returned is in column N. Your lookup value is in column A. You need to highlight the entire A to N range and then type 14 as your index number. If you happen to delete any of the in-between columns, you have to update that index number. You don’t need to worry about this when you use INDEX & MATCH.
- INDEX & MATCH can perform two-way lookups by both looking along the rows and along the columns to find the intersection within a matrix - even if you have multiple criteria. (Sure you can tweak VLOOKUP, but if you're going to do that, you might as well use INDEX.)
Watch this video tutorial for a simple explanation of INDEX & MATCH:
Download the free Excel workbook here: http://www.xelplus.com/excel-resources/ (there is a practice workbook for VLOOKUP on this link too - in case you want to know how that works...)
Have fun!
Just use PowerPivot ;)
I prefer linking tables (Excel 2013 and up); way easier than vlookup or index match
i hate counting columns so i will stick to I/M forever..:)
This is simply one of those necessary steps to evolution. Likewise, mastering dynamic named ranges. My advice when starting off is to have two helper columns, one for your Match result & one for your Index result. As you become more confident you will nest the Match inside the Index and then will never revert to VLookup. I campaigned years ago for a function called MINDEX which would wrap these two together and make it easier for the uninitiated and the idea seems to be getting some traction. I would say there is another positive spin off and that is it allows you to develop more of a data modelling head so from VLookup to Match/Index to PowerPivot!
Saying Goodbye to VLOOKUP won't be easy for most of the user. INDEX-MATCH is really good but not user friendly as well. If you messed with the formula, the sanctity of the data is at stack.