VLOOKUP

See also A-Z Spreadsheet Function List

Description 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)

search_item is 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 is 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 (e.g., -2, -1, 0, 2...A through Z, False, True). Text searches are not case-sensitive.

column_index is 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 rows in the search range.

If column_index is less than 1, #VALUE! is returned.

When column_index is greater than the number of rows 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, #REF! is returned.

See Also HLOOKUP , INDEX , LOOKUP , and MATCH functions

Examples In the preceding spreadsheet:

         VLOOKUP("Clark", A2:E9, 4) returns $28,700

         VLOOKUP("Lee", A2:E9, 3) returns 3961