VLOOKUP function explained with example

The VLOOKUP function is used to search for a value in the first column of a range and return a value in the same row from another column.

Syntax:

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

Parameters:

• lookup_value: The value you want to search for.
• table_array: The range of cells containing the data.
• col_index_num: The column number in the range (starting with 1) from which to return the value. Column index number 1 is the leftmost column in the table array, column index number 2 is the second column, and so on.
• range_lookup (optional): A logical value:
• TRUE: Approximate match.
• FALSE: Exact match.

Example:

You have a table in A1:C5:

ID Name Score

101 Alice 95

102 Bob 89

103 Charlie 92

104 Diana 87

Goal:

Find the score of Charlie.

Formula:

=VLOOKUP(“Charlie”, A1:C5, 3, FALSE)

Result: 92

Tips:

1. Case-insensitivity: VLOOKUP is case-insensitive (e.g., “Charlie” = “charlie”).
2. Sort Requirement:

• If range_lookup is TRUE, the first column must be sorted in ascending order.
• If range_lookup is FALSE, sorting is not required.

Error Handling:

#N/A: If the value is not found.

#REF!: If col_index_num exceeds the number of columns in table_array.

#VALUE!: If parameters are incorrect.