Vlookup and Hlookup

Excel spreadsheet file used in video: Right-click to download file

Vlookup

VLOOKUP stands for vertical lookup. VLOOKUP searches for a value in the first column of an array and then returns a value in another column indicated by the col_index_num
VLOOKUP takes 4 arguments:

• lookup_value: the value being looked up, must be in the first column of the table_array argument.
• table_array: The range of cells being searched.
• col_index_num: The column number for the value being returned (the leftmost column is column 1)
• range_lookup: If omitted or set to TRUE or 1, Table is sorted in ascending order, if an exact match is not found the next smallest value in the specified column will be returned. If set to FALSE or 0, VLOOKUP searches for an exact match. If not found it will return a #N/A error.

In the following example the range_lookup is set to 0, so an exact match is sought. VLOOKUP searches column 1 for a product, when the product is found the price in column 4 on the same row is returned.

In the next example the table is arranged in ascending order and the range_lookup is omitted. VLOOKUP searches column 1 for the value in A9, when the value is surpassed it jumps down one row to the next smallest value and returns the discount in column 2 on that row.

HLOOKUP

Hlookup is the horizontal version of Vlookup, it searches for a value in the first row of an array and then returns a value in another column indicated by the row_index_num. Hlookup takes 4 arguments:

• lookup_value: the value being looked up, must be in the first row of the table_array argument.
• table_array: The range of cells being searched.
• row_index_num: The row number for the value being returned (the top row is row 1)
• range_lookup: If omitted or set to TRUE or 1, Table is sorted in ascending order left to right, if an exact match is not found the next smallest value in the specified row will be returned. If set to FALSE or 0, HLOOKUP searches for an exact match. If not found it will return a #N/A error.

In the following example the range_lookup is set to 0, so an exact match is sought. HLOOKUP searches row 1 for a product, when the product is found the price in row 4 on the same row is returned.

In the next example the table is arranged in ascending order and the range_lookup is omitted. HLOOKUP searches row 1 for the value in A5, when the value is surpassed it goes to the left one column to the next smallest value and returns the grade in row 2 in that column.

VLOOKUP and HLOOKUP are not case sensitive.