VLOOKUP is a popular and useful function in Excel. It is one of the top 10 most popular Excel functions. It is the 3rd most common function behind SUM and AVERAGE. Every Excel user should know VLOOKUP.
Finally Learn 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 users learn. Many job interviewers 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.
In early 2020, Excel released the new XLOOKUP function. It will eventually replace both VLOOKUP and HLOOKUP. However, XLOOKUP is available only to Excel 365 users. See XLOOKUP: Meet The New Excel Boss for 2020.
How it is used
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 to 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 Excel is to watch a video tutorial. Here are the best VLOOKUP tutorials. This one is from Leila Gharani.
Tutorial video 1
Tutorial video 2
The second video is from Jon Acampora at Excel Campus. It is excellent also.
What questions do you have about VLOOKUP? Do you use it in your Excel spreadsheets? What questions do you have? Thanks for visiting Finally Learn.
Want to Finally Learn Excel? See Top 5 Places to Learn Excel for Free
