Using VLOOKUP, INDEX, and MATCH to find and extract data from tables and databases


VLOOKUP is a foundation function for using information in lists or databases. An easy way to remember what it does is that is a “V”ertical “Lookup”. I remember my mother showing me how to use a phone book (the internet wasn’t even a twinkle in Tim Berners Lee eye at the time). She had the 4” thick phone book open on our yellow Formica kitchen table.

“You know what alphabetical order is. The names are in alphabetical order. Look for the last name down the left column then, look across the list to the column that has the phone numbers. It’s that easy.”

It’s also that easy in Excel with VLOOKUP. VLOOKUP is one of Excel’s most useful functions. You can use VLOOKUP to look up prices for a product, taxes on an amount, contract numbers by vendor, or sales for a specific region. Once you have retrieved a cell’s content with VLOOKUP you can use Excel’s other functions on the result to do numeric calculations or text manipulation.

When you say to yourself, “How in the world can I do this with VLOOKUP?”, then it’s time to use INDEX and MATCH, a more flexible combination.

Once you feel comfortable using the basic VLOOKUP, I highly recommend learning how to use INDEX and MATCH. When you want to do basic look ups from a table or database VLOOKUP works fine, but when you find yourself saying, “How am I going to do this with VLOOKUP?”, then it’s time to use INDEX and MATCH. INDEX and MATCH are a powerful combination that gives you greater power and far more flexibility in how and where you can use it.

Download the Excel samples files for VLOOKUP