VLOOKUP is one of the most popular excel formulas that exists. Looking up and finding data is one of the most common uses of excel and VLOOKUP is many an excel user’s go-to formula. VLOOKUP has one very annoying limitation however; it can only search to the right of the table.
INDEX+MATCH solves this problem and also does a better job. On average, INDEX+MATCH has been found to be 5-10% faster than VLOOKUP and when you are working with huge sets of data, this stuff begins to make a difference.
Basic use
I would suggest you spend 5 minutes learning the syntax of INDEX+MATCH by heart as it will save you a ton of time.
Index(“column with data you want”, MATCH(“data you have”, “column which contains this data”,0))
Demonstration: I have demonstrated this in the INDEX+MATCH sheet in today’s spreadsheet. Please download it here or please feel free to send me an email with your dropbox ID and I’ll be happy to share the folder.
(Tip: When you open the sheet, press CTRL+~ to see al formulas in the sheet. Same combination to reverse the view)
Advanced use
INDEX+MATCH is a very powerful combination and has numerous advanced uses. I have illustrated one advanced use – you can use it to extract all unique values from a list.
Another useful use is to have embed an INDEX+MATCH within an INDEX+MATCH to search both horizontally and vertically. You’ll find these applications on google as and when you need it.
The point to remember here is that INDEX+MATCH is the way to go when building excel models. Learn how to use them and you’ll find multiple uses for this very powerful combination.
Look forward to your questions in the comments.
Series introduction, Part 1 (Set up), Part 2 (Shortcuts), Part 3(SUMIFS, COUNTIFS)
A Learning a Day 