In our Advanced Microsoft Excel 2010 training course, expert Microsoft trainer Guy Vaccaro teaches you about the lookup function in Excel, and how to perform a VLOOKUP and HLOOKUP.
What is VLOOKUP?
While VLOOKUP is an extremely useful function in Excel, it can also be tough to understand. In the VLOOKUP, the V stands for vertical. The VLOOKUP searches for a value in the first column of a table array. It retrieves information from a list based on a supplied instance of the unique identifier. Basically, if you put the VLOOKUP function into a cell and give it one of the unique identifiers from your list, it will return to you one of the pieces of information associated with that unique identifier.
You use VLOOKUP when your comparison values are located in a column to the left of the data you want to find. The syntax for VLOOKUP is Criteria, Lookup Table, Column to Return, True or False – the formula would look like this: VLOOKUP(lookup_value,table_array,col_index_num,range_lookup).
What does that mean?
Criteria is part A: a cell reference. This is when you take your criteria and look it up in the lookup table. Lookup Table is part B. A lookup table can exist in the same sheet or workbook, or a different workbook, and can even be a named cell reference. Column to Return is part C. Here, you will look down the first column to find the criteria. You then have to decide which of these related values you return, which is decided by the column to return. You run across to the column reference that you’re trying to find the value for and look for the value that you need to return back to the cell that made the lookup. True or False is part D, which decides whether there must be an exact match down the list. Using false, there has to be an EXACT match, meaning nothing can differ. Using true, Excel would look for the nearest match and then perform a lookup, so you can have slight differences here. True is used when you’re trying to look up between a range of numeric values.
What is HLOOKUP?
In HLOOKUP, the H stands for horizontal. This function performs a horizontal lookup by searching for a value in the top row of a table array, and returns the value in the same column based on the index number. It uses the exact same syntax as VLOOKUP, with the only difference being the makeup of the actual lookup table – the data is stored horizontally, so the first row is the lookup row, which changes the Column to Return aspect to “row” to return. The formula would be: HLOOKUP(lookup_value,table_array,row_index_num,range_lookup).
To sum it up, criteria must always exist in the first row. You will then take your criteria and look it up in the lookup table. Then we need the row to return, so you’ll go down to the column that finds your match, and then you return whichever row number you’re after. Then true or false – false being used for an exact match, and true for an approximation.
Once you understand the VLOOKUP and HLOOKUP functions in Excel, you will be able to perform lookups. Here are two tutorials from our Advanced Microsoft Excel 2010 training course that further explain the lookup functions in Excel.
Find a near match in a lookup: In this video, Guy will explore how to use lookup tables that do not have an exact match.
Checking for missing data in a lookup: In this video, follow along with Guy as he shows you how to effectively trap errors and not to display them so they don’t mess up your lookup.