Mastering VLOOKUP
VLOOKUP is probably one of the most commonly used but hated formulas. Ever thought "I've entered everything where it's supposed to go yet why is my result showing #N/A or #VALUE??"
Introduction
VLOOKUP is one of many Excel's lookup functions. It is used to help identify something (e.g. item name, price, unit sales) in a table or range being particularly useful when you have a large table / set of data.
Excel Formula: =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
Formula Made Easy
Example
Mad Hatter Chocolatier is a chocolate store which sells chocolates with a variety of flavours. Every month, the finance team receives raw data (RAW DATA tab) that shows many things, one of them being “Product Name”.
Unfortunately, the “Product Code” is not provided which they will need in order to do some analysis and to assist them to track orders and popularity of flavours.
There are two options to get the “Product Code”:
Long way: Go to the PRODUCT LIST tab, copy the “Product Code” and match every row accordingly.
Short way: Use VLOOKUP
Applying my example to the "Formula Made Easy" table:
Formula Applied
Putting the above references into the Excel formula, it will be:
=VLOOKUP(I2,Table1,2,0)
Download file to follow along here: Mad Hatter Chocolatier
Limitations to VLOOKUP
VLOOKUP only allows you to look up columns (vertical), hence why it’s call VLOOKUP
The value I want to lookup (lookup_value) must be stored in the first (left hand) column of the table / range
If that is not the case, don’t go re-arranging your columns. There is another formula called INDEX-MATCH which solves the problem ☺
Comments