VLOOKUP function

1 year ago

4 minutes

The VLOOKUP function in Excel is used to search for a value in the first column of a table or range and return a value in the same row from another column in that range. The “V” stands for vertical, meaning it searches vertically down the first column.

Syntax

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

Parameters

lookup_value: The value you want to search for in the first column of the table.
table_array: The range of cells that contains the data. The first column should contain the lookup_value you want to search.
col_index_num: The column number in the table_array from which to return the value. The first column is 1.
[range_lookup] (optional): A logical value that specifies whether to find an exact match or an approximate match:
- TRUE or omitted: Finds an approximate match. The first column must be sorted in ascending order.
- FALSE: Finds an exact match. If no exact match is found, it returns #N/A.

How It Works

The VLOOKUP function looks for the lookup_value in the first column of the table_array. If it finds a match, it returns the value in the same row from the column specified by col_index_num.

Examples

Example 1: Exact Match Lookup

Suppose you have the following table of data in cells A2:B5:

A	B
ID	Name
101	John
102	Mary
103	Alex

To find the name associated with ID 102, use this formula:

=VLOOKUP(102, A2:B5, 2, FALSE)

Explanation:

102 is the lookup_value.
A2:B5 is the table_array that contains the data.
2 means we want to return the value from the second column (Name column).
FALSE specifies that we want an exact match.

The result will be Mary.

Example 2: Approximate Match Lookup

Suppose you have the following grade thresholds in cells A2:B6:

A	B
Score	Grade
0	F
60	D
70	C
80	B
90	A

If you want to look up a grade for a score of 75 (approximate match), you can use:

=VLOOKUP(75, A2:B6, 2, TRUE)

Explanation:

75 is the lookup_value.
A2:B6 is the table_array that contains the data.
2 means we want to return the value from the second column (Grade column).
TRUE specifies an approximate match.

The result will be C, because 75 falls between 70 and 80, and the closest lower value is 70 (which corresponds to grade “C”).

Example 3: Using VLOOKUP to Retrieve Multiple Columns

You can use VLOOKUP to retrieve information from multiple columns by writing separate VLOOKUP functions. Suppose you have a table in cells A2:C5:

A	B	C
ID	Name	Age
101	John	25
102	Mary	30
103	Alex	22

To find the name and age of the person with ID 103, you can use:

For Name:
```
=VLOOKUP(103, A2:C5, 2, FALSE)
```
For Age:
```
=VLOOKUP(103, A2:C5, 3, FALSE)
```

The results will be Alex and 22.

Common Errors in VLOOKUP

#N/A: This occurs if no match is found. It can also happen if you have set range_lookup to FALSE and the lookup_value is not exactly found in the first column of table_array.
#REF!: This occurs if col_index_num is greater than the number of columns in the table_array.
#VALUE!: This error occurs if the col_index_num argument is less than 1 or not a number.

Things to Keep in Mind

Search Range: VLOOKUP only searches for the value in the first column of the specified range.
Exact vs. Approximate Match: If you’re looking for an exact match, always use FALSE for the range_lookup argument. If you’re looking for an approximate match, ensure that the first column of your data is sorted in ascending order.
Column Index Number: The column number you specify (col_index_num) should be within the table array; otherwise, you’ll get an error.

Alternatives to VLOOKUP

XLOOKUP: A more modern function that replaces VLOOKUP with more flexibility, such as searching in any direction and returning multiple results.
INDEX-MATCH: This is a more flexible combination that can be used instead of VLOOKUP, especially when the search column is not the first column in your table.

Post Views: 33