VLOOKUP finds a value in the first column of a table and returns a value from another column in the same row. It is one of the most asked Excel functions in interviews and one of the most useful at work.
The VLOOKUP syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value: what you are searching for, such as an employee ID.
- table_array: the table to search. The lookup value must be in its first column.
- col_index_num: which column of the table to return, counted from the left (the first column is 1).
- range_lookup: use
FALSE(or 0) for an exact match. UseTRUEonly for approximate matches.
A simple example
Say employee IDs are in column A and salaries are in column C of the range A2:C100. To find the salary of the ID typed in F2:
=VLOOKUP(F2, A2:C100, 3, FALSE)
Excel looks for F2 in column A and returns the value from the 3rd column of the range.
Step by step
- Click the cell where you want the result.
- Type
=VLOOKUP(and click the cell with your lookup value. - Select the table and press F4 to lock it as an absolute reference, like
$A$2:$C$100. - Type the column number and
FALSE, then close the bracket and press Enter.
Fixing a #N/A error
#N/A usually means the lookup value was not found. Check for extra spaces, numbers stored as text, or a lookup value that is not in the first column. To show a clean message instead, wrap it: =IFERROR(VLOOKUP(F2, A2:C100, 3, FALSE), "Not found").
VLOOKUP limits
VLOOKUP cannot look to the left of the lookup column, and it breaks if you insert a column in the table. If you have a newer Excel, XLOOKUP avoids both problems.