Documentation/Calc Functions/LOOKUP

Function name:
LOOKUP

Category:
Spreadsheet

Summary:
Performs a lookup on a one-dimensional cell range (also known as a vector). Compares a given value with the entries in the ordered search vector and, when a match is found, returns the content of the corresponding cell in a result vector.

The matching process can utilize either wildcards or regular expressions. By default, the matching is not case-sensitive, but a case-sensitive match can be carried out when using a regular expression.

Syntax:
LOOKUP(Search criterion; Search vector[; Result vector])

Returns:
Returns the content of a cell within the result vector (if specified) or a value from the search vector (no result vector specified). The data returned may be either numeric or non-numeric depending on context.

Arguments:
Search criterion is a text string (in quotation marks), a number, a Boolean, or a reference to a cell containing one of those types. Search criterion is matched against the contents of cells in Search vector.

Search vector specifies a range of cells, typically defined by entering the cell reference for the upper left-hand cell, followed by a colon, and then the lower right-hand cell reference (for example, A1:E10). It may also be specified using the name of a named range or a database range, or may be passed as an inline array. If Search vector is a multi-dimensional cell range, LOOKUP examines its dimensions and proceeds as follows: The vector to be searched must be sorted, as described in the section below, and this enables LOOKUP to produce both exact and inexact (best-fit) matches.
 * If the range is square, or higher than it is wide, then LOOKUP matches Search criterion against the contents of the first column of Search vector. In this case, if the Result vector argument is omitted, a value from the last column of Search vector is returned.
 * If the range is wider than it is high, then LOOKUP matches Search criterion against the contents of the first row of Search vector. In this case, if the Result vector argument is omitted, a value from the last row of Search vector is returned.

Result vector specifies the one-dimensional range of cells containing values to be returned. This range is typically defined by entering the cell reference for the first cell, followed by a colon, and then the last cell reference (for example, A1:A10). It may also be specified by using the name of a named range or a database range, or may be passed as an inline array.


 * If the search vector is not ordered as expected, then the result returned by LOOKUP is unpredictable. In such cases, it is possible that no error will be reported and it may not be obvious that the returned result is meaningless.


 * If the Search criterion argument would precede the content of the first cell of the search vector, then LOOKUP reports the #N/A error (value not available).


 * If Result vector is a multi-dimensional cell range, then LOOKUP reports an error in the parameter list (Err:504).


 * The lengths of the search vector and the result vector do not need to be the same, and one could be a column vector while the other is a row vector.
 * If Result vector is an inline array and the match position falls outside its length, then LOOKUP reports a value not available error (#N/A).
 * If Result vector is a cell range and the match position falls outside its length, it gets automatically extended to the length of Search vector, but in the direction of Result vector. If Result vector is just a single cell reference (and hence has no implied direction), LOOKUP extends it as a column vector.

Details specific to LOOKUP function

 * If the vector to be searched contains multiple cells with the same content, LOOKUP will only match one of the cells sharing common content and this could be a cause of confusion.


 * The vector to be searched should be sorted with numbers in ascending order appearing before text values in alphabetic order. Boolean values FALSE and TRUE are treated as numbers 0 and 1 respectively. LOOKUP calculates where Search criterion would appear in the sorted order.
 * If there is an exact match between Search criterion and one of the entries in the search vector, that position is used to determine the return value.
 * If there is no exact match between Search criterion and any of the entries in the search vector, LOOKUP matches the largest value in the search vector that is less than Search criterion.


 * If you intend to pass a multi-dimensional cell range for the Search vector argument, consider using instead one of Calc’s more flexible HLOOKUP and  VLOOKUP functions.


 * If regular expressions are enabled in the Formulas Wildcards area of the (or  on macOS) dialog, then LOOKUP will find exact matches treating Search criterion as a regular expression.


 * If wildcards are enabled in the Formulas Wildcards area of the (or  on macOS) dialog, then LOOKUP will find exact matches treating Search criterion as a wildcard expression.


 * If the Search criteria = and <> must apply to whole cells option is ticked in the General Calculations area of the (or  on macOS) dialog, then Search criterion must match the whole text in a cell; if not, it can match just part of the text.


 * The Case sensitive option in the General Calculations area of the (or  on macOS) dialog has no effect on the operation of LOOKUP.

Examples:
The following table shows the name, identification number, and exam score for ten students. The table is ordered alphabetically by the student’s name and so the data in cells A2:A11 can be used as the search vector in calls to the LOOKUP function. This simple table can be used to demonstrate many of the behaviors of LOOKUP.

The following table and related examples utilize a search vector that includes numbers, Booleans, and text strings.

The final example shows the use of inline arrays for the Search vector and Result vector arguments.

Related LibreOffice functions:
HLOOKUP

INDEX

MATCH

OFFSET

VLOOKUP

ODF standard:
Section 6.14.8, part 2

Equivalent Excel functions:
LOOKUP