Syntax
Definition. This function finds values in an array (a cell range or an array constant). If the array consists of only one column or row, the vector version is used.
The vector version looks for a value in a vector and returns the value at the same position in the second vector.
The array version looks for a value in the first row or column of an array and returns the value at the same position in the last row or column of the same array.
Arguments (Vector Version)
lookup_value (required). Must evaluate to a number, string, or logical value. This argument can be a reference to a cell containing the value.
lookup_vector (required). A reference to a cell range consisting of one row or one column. The elements contained in the lookup_vector can be strings, numbers, or logical values.
result_vector (required). Similar to the lookup_vector consisting of one row or one column. This argument must include the same number of elements as the lookup_vector. If this argument is omitted, the array version of the function and not the vector version is automatically assumed.
Arguments (Array Version)
lookup_value (required). Must evaluate to a number, string, or logical value. This argument can be a reference to a cell containing the value.
array (required). A reference to a cell range containing text, numbers, or logical values.
Note
You can use array constants instead of cell references. Array constants are values enclosed in braces. Rows are separated by a semicolon and columns by a comma. For example, {1;2;3;4} is a vector consisting of one column and four rows. {11,12,13;21,22,23} is an array consisting of three columns and two rows.
Background. In both the vector and array versions, the values you are searching must be arranged in ascending order; otherwise, LOOKUP() might return an unexpected or even wrong value. Unlike the VLOOKUP() and HLOOKUP() functions, with LOOKUP() you cannot further specify the search.
Strings (text) are not case-sensitive.
If the LOOKUP() function cannot find a value matching the search criteria, it uses the largest value in lookup_vector smaller than or equal to lookup_value. If lookup_value is smaller than the smallest value in lookup_vector, the LOOKUP() function returns the #N/A error.
In the array version, the size of the array determines the search range. If the array has more columns than rows, the function looks in the first row for the value. Otherwise, it looks in the first column. If no matching value is found, the largest value smaller than or equal to lookup_value is used. If lookup_value is smaller than all of the values, the function returns the #N/A error.
Example. The array version of LOOKUP() is similar to the HLOOKUP() and VLOOKUP() functions. The difference is that HLOOKUP() compares the first row and VLOOKUP() compares the first column; and LOOKUP() compares the row or column with lookup_value. With HLOOKUP() and VLOOKUP(), you can skip to a certain row or column by specifying an index. This is more flexible than the LOOKUP() function, which always returns the value in the last row or column.
For a dynamic list to which items might be added, there are usually fewer columns than rows, and you cannot search in the headers to get the information in the last row. For this situation, you can use HLOOKUP() together with ROWS() or, alternatively, MATCH() in combination with INDEX().
Excel is not a substitute for a database management system (such as Microsoft Access or Microsoft SQL Server). However, if you save data lists in worksheets, you can use the vector version of LOOKUP() instead of VLOOKUP() to search multiple contiguous ranges on different worksheets. For this to work, the lookup vectors must be sorted and the result vectors must match the lookup vectors.