LOOKUP Function in Excel

The VLOOKUP function searches for a value in the first column of a range (or table) and returns a value in the same row from another column.

Syntax

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

Arguments

1. lookup_value: The value you want to find in the first column of the table.
2. table_array: The range of cells that contains the data.
3. col_index_num: The column number in the range from which to retrieve the value.
4. range_lookup (optional):

• TRUE (default): Finds an approximate match. The data in the first column must be sorted in ascending order.
• FALSE: Finds an exact match.

Examples

1. Basic Example:

• Formula: =VLOOKUP(101, A2:C10, 3, FALSE)
• Searches for the value 101 in column A and returns the value in column 3 of the same row.

1. Approximate Match:

• Formula: =VLOOKUP(90, A2:B10, 2, TRUE)
• Finds the closest smaller or equal value to 90 in column A and returns the value in column 2.

Common Errors

1. #N/A: Value not found.

• Ensure the lookup value exists in the first column of the table.

1. #REF!: Invalid column index number.

• The col_index_num exceeds the number of columns in the table_array.

1. #VALUE!: Incorrect data type.

• Ensure the arguments are correctly entered.

Tips

• Use FALSE for exact matches when working with unique identifiers (e.g., product codes).
• Consider using XLOOKUP (available in newer Excel versions) for a more flexible and robust alternative.