VLOOKUP
Description
VLOOKUP searches the first column of a table for a value and returns the contents of a cell in that table that corresponds to the location of the search value.
Syntax
VLOOKUP ( search_item, search_range, column_index )
|
Parameter |
Description |
|
search_item |
A value, text string, or reference to a cell containing a value that is matched against data in the top row of search_range. |
|
Search_range |
The reference of the range (table) to be searched. The cells in the first column of search_range can contain numbers, text, or logical values. The contents of the first column must be in ascending order (for example, -2, -1, 0, 2...A through Z, False, True). Text searches are not case-sensitive. |
|
column_index |
The column in the search range from which the matching value is returned. column_index can be a number from 1 to the number of columns in the search range. If column_index is less than 1, #VALUE! is returned. When column_index is greater than the number of columns in the table, #REF! is returned. |
Remarks
VLOOKUP compares the information in the first column of search_range to the supplied search_item. When a match is found, information located in the same row and supplied column (column _index) is returned.
If search_item cannot be found in the first column of search_range, the largest value that is less than search_item is used. When search_item is less than the smallest value in the first column of the search_range, #N/A is returned.
Examples
The following examples use this worksheet.
This function returns $28,700:
VLOOKUP("Clark", A2:E9, 4)
This function returns 3961:
VLOOKUP("Lee", A2:E9, 3)
Note:
If an Invalid formula syntax warning appears, check whether the comma is set as the list separator in your regional settings on the Windows Control Panel. If a different character is set as the list separator, use this character in the above examples in place of the comma. (Windows XP: Access the Windows Control Panel and select Regional and Language Options. On the Regional Options tab page, click Customize. On the Numbers tab page, check which character is the default list separator on your computer. Use this character in the above examples instead of the comma.
Also, see