VLOOKUP function

VLOOKUP is one of the lookup and reference functions you can use when you need to find things in a table or a range by row.
For example, look up an employee’s last name by their employee number, or find their phone number by looking up their last name (just like a telephone book).

The secret to VLOOKUP is to organize your data so that the value you look up (employee’s last name) is to the left of the return value you want to find (employee’s phone number).


VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])


We want to look up the ID number for Gina Wilson in the range below

The VLOOKUP formula to do this would be

=VLOOKUP (“Gina Wilson”, A1:C20, 3, 0)

lookup_value : The value we want Excel to look for in the first column of the given range.

table_array: The range of cells we want Excel to look in.

col_index_num: The column containing the value we want Excel to return.

[range_lookup]: Exact match, we want Excel to find this exact name.

HLOOKUP function

HLOOKUP searches for a value in the top row of a table or an array of values, and then returns a value in the same column from a row you specify in the table or array. Use HLOOKUP when your comparison values are located in a row across the top of a table of data and you want to look down a specified number of rows.
Use VLOOKUP when your comparison values are located in a column to the left of the data you want to find.

The H in HLOOKUP stands for “Horizontal.”


HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup])

Video Tutorial