VLOOKUP Tutorial and 3 Easy Tips

VLOOKUP and how you should it

Summary

The vlookup Excel formula searches your data for a keyword and returns a value instantly.  Think of how you order at Starbucks: If I want to know how much an Iced 

Coffee costs, I would find the Iced Coffee on the menu (2nd from the top), and

 

look to the right to see that a small costs $2.25.  Excel does the same thing, except it can search 100,000 items, in the same amount of time it takes you to search through 10.

Real Life Examples

Lets keep using the Starbucks menu for this example.  We want to know how much a White Chocolate Mocha would cost if we purchased the Grande size.  In this example, the “lookup_value” is in cell A12 ‘White Chocolate Mocha’, the “table_array” is A2:D9, the “col_index_num” is 3 and “[Range lookup]” is false. What vlookup is doing, is searching for ‘White Chocolate Mocha’ in the table we highlighted in red, and looking at the third column.  If we wanted the price of a Venti, we would have used 4 instead of 3 as the “col_index_num”.

IMPORTANT NOTE:  “lookup_value” aka White Chocolate Mocha always needs to be in the first column in the table.

Common Issues

  • You didn’t anchor your formulas.
    • If you copy the formula to the cell below, then the table array will also shift down one row.
    • To prevent this, highlight the table array in the formula bar, and press F4 (on Windows) to add anchors (signified by dollar signs).  This tells Excel not to move whatever you’ve anchored.
  • The thing you’re looking for shows up more than once.
    • When this happens, Excel returns the first thing it finds (going from top to bottom).
    • Avoid using names if possible (John, Sanjay, Allison) and try and use a full name or an ID number.  If I have data for multiple sales, there could be multiple salespeople named John.  Instead, if I wanted to know about a specific sale, I could see if there was an ID with the order.
    • If I’m more interested in the amount of money that Sales Rep John generated, I could use a different formula like Sumif() or sumifs().
  • Your “Find This” keyword, isn’t in the first column.
    • Always make sure that the table you’re searching from, includes the keyword as the first column.
  • Not using false as the [range_lookup]
  • The Find this doesn’t match the thing your searching for.  If you’re “Find This” term is “Jen”, but the value in the table is “Jenny”, “Jenn”, or Jennifer”, your vlookup won’t know how to find it.  Make sure the thing you look for uses the same names.

Next Steps

Once you’ve used the vlookup enough, you’ll notice that you want something more powerful.  This might mean you’re ready to move to the “index match” formula.