Documentation/Calc Functions/LOOKUP
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
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:
- 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.
The vector to be searched must be sorted, as described in the #Additional details: section below, and this enables LOOKUP to produce both exact and inexact (best-fit) matches.
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.
Additional details:
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.
General information about Calc's regular expressions
 |
Note:
|
- A regular expression is a string of characters defining a pattern of text that is to be matched. More detailed, general background information can be found on Wikipedia’s Regular expression page.
- Regular expressions are widely used in many domains and there are multiple regular expression processors available. Calc utilises the open source Regular Expressions package from the International Components for Unicode (ICU). See their Regular Expressions documentation for further details, including a full definition of the syntax for ICU Regular Expressions.
- In addition, the LibreOffice Help system provides a high-level list of regular expressions.
- Calc’s regular expression engine supports numbered capture groups, which allow sub-ranges within a match to be identified and used within replacement text. Parentheses are used to group components of a regular expression together and create a numbered capture group. To insert a capture group into a replacement text, use the "$n" form, where n is the number of the capture group.
General information about Calc's wildcards
Wildcards are special characters that can be used in search strings passed as arguments to some Calc functions; they can also be used to define search criteria in the Find & Replace dialog. The use of wildcards enables the definition of more advanced search parameters with a single search string.
Calc supports either wildcards or regular expressions as arguments, depending on the current application settings. By default, wildcards are supported instead of regular expressions.
To make sure wildcards are supported, go to ▸ ▸ ▸ and check whether the option Enable wildcards in formulas is selected. Note that you can use this dialog to switch to regular expressions by choosing Enable regular expressions in formulas or choose to support neither wildcards nor regular expressions.
The following table identifies the wildcards that Calc supports.
| Wildcard | Description |
|---|---|
| ? (question mark) | Matches any single character. For example, the search string "b?g" matches "bag" and "beg" but will not match "boog" or "mug". Note that it will not match "bg" either, since "?" must match exactly one character. The "?" wildcard does not correspond to a zero-character match. |
| * (asterisk) | Matches any sequence of characters, including an empty string. For example, the search string "*cast" will match "cast", "forecast", and “outcast”, but will not match "forecaster" using default Calc settings. If the option Search criteria = and <> must apply to whole cells is disabled in ▸ ▸ ▸ , then "forecaster" will be a match using the "*cast" search string.
|
| ~ (tilde) | Escapes the special meaning of a question mark, asterisk, or tilde character that follows immediately after the tilde character. For example, the search string "why~?" matches "why?" but will not match "whys" nor "why~s". |
Wildcard comparisons are not case sensitive, hence "A?" will match both "A1" and "a1".
These wildcards are supported in both Calc and Microsoft Excel. Therefore, if interoperability between the two applications is needed, choose to work with wildcards instead of regular expressions. Conversely, if interoperability is not necessary, consider using regular expressions for more powerful search capabilities.
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.
| A | B | C | |
|---|---|---|---|
| 1 | Name | Student ID | Exam Score |
| 2 | Andrew | 1257 | 32 |
| 3 | Bethany | 1470 | 80 |
| 4 | Charles | 1367 | 42 |
| 5 | David | 1497 | 65 |
| 6 | Emily | 1730 | 50 |
| 7 | Ferdinand | 1414 | 41 |
| 8 | Georgia | 1364 | 17 |
| 9 | Haley | 1778 | 46 |
| 10 | Ian | 1769 | 90 |
| 11 | Jennifer | 1530 | 35 |
| Formula | Description | Returns |
|---|---|---|
| =LOOKUP("Haley"; A2:C11) | Here the Search vector argument is a multi-dimensional cell range (with height greater than width) and the Result vector argument is omitted. The function finds the string "Haley" in the first column of Search vector and returns that student's exam score from the corresponding position in the last column. The formula =LOOKUP("Haley"; A2:A11; C2:C11) would give the same result. |
46 |
| =LOOKUP(G2; A2:C11) where cell G2 contains the string "Ailsa" (entered without quotation marks). | Here the Search vector argument is again a multi-dimensional cell range (with height greater than width) and the Result vector argument is omitted. The function searches for the string "Ailsa" in the first column of Search vector but since "Ailsa" is alphabetically before the first name in the list ("Andrew"), an error is reported. The formula =LOOKUP(G2; A2:A11) would give the same result. |
#N/A |
| =LOOKUP("Ken"; A2:C11) | Here the Search vector argument is again a multi-dimensional cell range (with height greater than width) and the Result vector argument is omitted. The function searches for the string "Ken" in the first column of Search vector and finds that "Ken" is alphabetically after the last name in the list ("Jennifer"). The exam score of the last person in the list is returned. The formula =LOOKUP("Ken"; A2:A11; C2:C11) would give the same result. |
35 |
| =LOOKUP("Evan"; A2:A11) | Here the Search vector argument is a one-dimensional cell range and the Result vector argument is omitted. The function searches for the string "Evan" in the first column of Search vector and finds that "Evan" appears after "Emily" but before "Ferdinand" in the sorted list. It therefore returns the string "Emily". | Emily |
| =LOOKUP("David"; A2:A11; B2:B11) | Here the Search vector argument is a one-dimensional cell range and the Result vector argument is specified. The function finds the string "David" in Search vector and returns that student's identification number from the corresponding position in the Result vector. The formulas =LOOKUP("David"; A2:B11) and =LOOKUP("David"; A2:A11; B2:B3)would give the same result. |
1497 |
The following table and related examples utilize a search vector that includes numbers, Booleans, and text strings.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | −1 | FALSE | TRUE | 3 | Cloud | Rain | Raincoat | Sun |
| 2 | ||||||||
| 3 | Pos 1 | Pos 2 | Pos 3 | Pos 4 | Pos 5 | Pos 6 | Pos 7 | Pos 8 |
| Formula | Description | Returns |
|---|---|---|
| =LOOKUP(−0.5; A1:H1; A3:H3) | Here the function determines that the given Search criterion lies between the values -1 and FALSE (0) in Search vector. LOOKUP matches the lower value and returns the corresponding entry in the result vector. | Pos 1 |
| =LOOKUP(TRUE; A1:H1; A3:H3) | Here the function determines that the given Search criterion matches the value in cell C1 within Search vector. LOOKUP returns the corresponding entry in the result vector. | Pos 3 |
| =LOOKUP("Area"; A1:H1; A3:H3) | Here the function determines that the given Search criterion lies alphabetically before any other text string within Search vector and returns the #N/A error. | #N/A |
| =LOOKUP("^c.*"; A1:H1; A3:H3) with regular expressions enabled. | The function matches the regular expression "^c.*" in the search vector ("Cloud") and returns the corresponding entry in the result vector. | Pos 5 |
| =LOOKUP("^ra.*"; A1:H1; A3:H3) with regular expressions enabled. | The function matches the regular expression "^ra.*" in the search vector ("Raincoat") and returns the corresponding entry in the result vector. However, this may not be particularly useful when there are multiple entries that match the regular expression (as in this case). | Pos 7 |
| =LOOKUP("s*"; A1:H1; A3:H3) with wildcards enabled. | The function matches the wildcard expression "s*" in the search vector ("Sun") and returns the corresponding entry in the result vector. | Pos 8 |
| =LOOKUP("ra*"; A1:H1; A3:H3) with wildcards enabled. | The function matches the wildcard expression "ra*" in the search vector ("Raincoat") and returns the corresponding entry in the result vector. However, this may not be particularly useful when there are multiple entries that match the wildcard expression (as in this case). | Pos 7 |
The final example shows the use of inline arrays for the Search vector and Result vector arguments.
| Formula | Description | Returns |
|---|---|---|
| =LOOKUP(5; 1, 2, 3, 4, 5, 6, 7, 8, 9, 10; {"Hydrogen", "Helium", "Lithium", "Beryllium", "Boron", "Carbon", "Nitrogen", "Oxygen", "Fluorine", "Neon"}) | Here the function returns the name of the element that has an atomic number of 5. | Boron |
Related LibreOffice functions:
ODF standard:
Related (or similar) Excel functions:
LOOKUP