VLOOKUP is a popular and useful function in Excel. It is one of the top 10 most popular Excel functions. Every Excel user should know VLOOKUP.
The three most popular Excel functions are:
- SUM
- AVERAGE
- VLOOKUP
VLOOKUP
VLOOKUP is so popular for a reason. It has the ability to extract data from a table, given a number or a label. This is so useful, and it is one of the first functions that Excel users learn. Many job interviews specifically ask about VLOOKUP.
VLOOKUP retrieves data from a specific column in a table in Excel. It supports approximate and exact matching and wildcards (* and ?) for partial matches. The “v” stands for “vertical.”
Lookup values must be in the first column of the table, with lookup columns to the right.
If the data in Excel resides in horizontal rows rather than columns, use the HLOOKUP function. Yes, “H” means “horizontal.” The HLOOKUP function is the same except it searches from top to bottom rather than left to right.
Excel has a newer XLOOKUP function. It can perform the same lookups as both VLOOKUP and HLOOKUP. XLOOKUP is available in Excel 365.
See the Excel XLOOKUP Tutorial for Beginners.
VLOOKUP Explained
In the table above, if you know the part number, you can retrieve any data to the right. So, it can pull the name, color, price, or quantity. In this example, Part 105 has a price of $13.50.
If you change the part number to 109, VLOOKUP will retrieve $18.00.
In the second example, if you know the name, you can retrieve any data to the right. So, you can find color, price, or quantity. However, if you search on the name, it cannot retrieve the part number because it is on the left of the name column.
VLOOKUP Excel formula
Here is how you build the formula:
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
Here are the definitions for the argument names:
- lookup_value – The value to look for in the first column of a table.
- table_array – The table or range from which to retrieve a value.
- col_index_num – The column in the table from which to retrieve a value.
- range_lookup – [optional] TRUE = approximate match (default). FALSE = exact match
VLOOKUP tutorial videos
The best way to learn VLOOKUP in Excel is to watch a video tutorial.
Here is a VLOOKUP tutorial for beginners.
Here is another VLOOKUP tutorial.
Want to Finally Learn Excel? See Top 5 Places to Learn Excel for Free
Jeff Mankin teaches financial literacy and Excel. He is the founder of Finally Learn.
