HLOOKUP can do exactly what VLOOKUP can do. Yes, really. It works with rows rather than columns. VLOOKUP is one of the top 10 most popular Excel functions. HLOOKUP is much less popular.
Contents
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 (* ?) 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 2019, Excel released a new XLOOKUP function. It is in beta release for several months. It could eventually replace both VLOOKUP and HLOOKUP. However, XLOOKUP is available only to a limited number of Excel users. See XLOOKUP: Meet The New Excel Boss.
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 109, VLOOKUP will retrieve $18.00.
[mc4wp_form id=”616″]
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 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
Jeff Mankin teaches financial literacy and Excel. He is the founder of Finally Learn.
