askvity

What is VLOOKUP in Excel?

Published in Excel Functions 4 mins read

VLOOKUP in Excel is a powerful function that searches for a specific value in the first column of a table and then returns a corresponding value from a column you specify within that same row. The "V" stands for "Vertical" because it searches vertically down the first column.

In essence, VLOOKUP helps you find related information in a table by using a lookup value. Think of it as an automated way to look something up in a directory.

Here's a breakdown of what VLOOKUP does and how it works:

How VLOOKUP Works

The VLOOKUP function requires four arguments:

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

Let's break down each argument:

  • lookup_value: This is the value you are searching for in the first column of the table. It can be a number, text, date, or even a cell reference.
  • table_array: This is the range of cells that make up the table where you're searching for the lookup_value and the data you want to retrieve. Crucially, the lookup_value must be in the first column of this range.
  • col_index_num: This is the column number within the table_array that contains the value you want to return. The first column in the table_array is column 1, the second is column 2, and so on.
  • [range_lookup]: This is an optional argument that specifies whether you want an exact match or an approximate match.
    • TRUE or omitted: VLOOKUP will find an approximate match. The first column in the table_array must be sorted in ascending order for this to work correctly. If an exact match is not found, it returns the next largest value that is less than the lookup_value.
    • FALSE: VLOOKUP will find an exact match. If an exact match is not found, it returns an error (#N/A). This is usually the preferred option.

Example

Let's say you have a table of employee information like this:

Employee ID Name Department Salary
101 Alice Smith Marketing 60000
102 Bob Johnson Sales 75000
103 Carol Lee Engineering 90000

You want to find the department of employee with ID 102. You can use the following VLOOKUP formula:

=VLOOKUP(102, A1:D3, 3, FALSE)
  • lookup_value: 102 (The Employee ID you're searching for)
  • table_array: A1:D3 (The entire table of employee data)
  • col_index_num: 3 (The Department is in the third column)
  • range_lookup: FALSE (You want an exact match)

This formula would return "Sales".

Key Considerations

  • Exact vs. Approximate Match: Using FALSE for an exact match is generally recommended to avoid unexpected results.
  • First Column: VLOOKUP always searches in the first column of the table_array.
  • Error Handling: If VLOOKUP doesn't find a match (and you're using FALSE), it returns the #N/A error. You can use the IFERROR function to handle these errors gracefully (e.g., =IFERROR(VLOOKUP(…), "Not Found")).
  • Alternatives: While VLOOKUP is widely used, the INDEX and MATCH functions together provide more flexibility, and XLOOKUP (available in newer Excel versions) is often a better alternative as it overcomes many of VLOOKUP's limitations.

VLOOKUP is a useful tool for retrieving information from tables in Excel, especially when dealing with large datasets. Understanding its arguments and limitations is key to using it effectively.

Related Articles