Documentation/Calc Functions/LOOKUP

From The Document Foundation Wiki
Jump to navigation Jump to search


Function name:

LOOKUP

Category:

Spreadsheet

Summary:

Returns the contents of a cell either from a one-row or one-column range. Optionally, the assigned value (of the same index) is returned in a different column and row. As opposed to VLOOKUP and HLOOKUP, search and result vector may be at different positions; they do not have to be adjacent. Additionally, the search vector for the LOOKUP must be sorted ascending, otherwise the search will not return any usable results.

Syntax:

LOOKUP(SearchCriterion; SearchVector[; ResultVector])

Returns:

Returns a value contained in a cell which is the SearchCriterion or a value just smaller than it finding from SearchVector and optionally, the assigned value (of the same index) is returned in a different column and/or row mentioned via ResultVector argument.

Arguments:

SearchCriterion is the value to be searched for entered either directly or as a reference to a cell containing that value.

SearchVector is a reference to the single-row or single-column area to be searched. It can be an array object as well.

ResultVector is a reference to another single-row or single-column range from which the result of the function is taken. The result is the cell of the result vector with the same index as the instance found in the search vector. It can be an array object as well.

See Help page entitled Handling of Empty Cells.

  • The lengths of the SearchVector and the ResultVector vector do not need to be identical. When the match position falls outside the length of the ResultVector, an error is returned if the result vector is given as an array object. If it is a cell range, it gets automatically extended to the length of the SearchVector, but in the direction of the ResultVector. If just a single cell reference was passed, a column vector is generated. If the cell range cannot be extended due to the sheet's size limit, then the #N/A Error is returned.

Additional details:

  • Sorted ascending includes smaller Text values before larger ones (e.g., "A" before "B", and "B" before "BA"), and False before True.
  • If the types are mixed, Numbers are sorted before Text, and Text before Logicals. Evaluators without a separate Logical type may include a Logical as a Number.
  • If LOOKUP cannot find the SearchCriterion, it matches the largest value in the search vector that is less than or equal to the search criterion.
  • Interoperability is improved by the use of HLOOKUP or VLOOKUP in expressions over LOOKUP.
  • When given two parameters, SearchVector is first examined:
    • If SearchVector is square or is taller than it is wide (more rows than columns), LOOKUP searches in the first column (similar to VLOOKUP), and returns the corresponding value in the last column.
    • If SearchVector covers an area that is wider than it is tall (more columns than rows), LOOKUP searches in the first row (similar to HLOOKUP), and returns the corresponding value in the last row.
  • When given 3 parameters, ResultVector shall be a vector (either a row or a column) or an Error is raised. The function determines the index of the match in the first column respectively row of SearchVector, and returns the value in ResultVector with the same index.
  • The values returned may vary depending upon the HOST-USE-REGULAR-EXPRESSIONS or HOST-USE-WILDCARDS or HOST-SEARCH-CRITERIA-MUST-APPLY-TO-WHOLE-CELL properties. You can know more about it in Host-Defined Behaviors.
  • The search supports wildcards or regular expressions. With regular expressions enabled, you can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every character with a "\" character, or enclose the text into \Q...\E. You can switch the automatic evaluation of wildcards or regular expression on and off in Tools - Options - LibreOffice Calc - Calculate.
Gnome-dialog-warning.svg

Warning:
When using functions where one or more arguments are search criteria strings that represents a regular expression, the first attempt is to convert the string criteria to numbers. For example, ".0" will convert to 0.0 and so on. If successful, the match will not be a regular expression match but a numeric match. However, when switching to a locale where the decimal separator is not the dot makes the regular expression conversion work. To force the evaluation of the regular expression instead of a numeric expression, use some expression that can not be misread as numeric, such as ".[0]" or ".\0" or "(?i).0".


Examples:

A B
1 Employee Salary
2 Hans 21682
3 Brigitte 25250
4 Ute 46782
5 Fritz 54806
Formula Description Returns
=LOOKUP(30000;B2:B5) The function returns that employee's salary which is just less than 30000 in column B. 25220
=LOOKUP(30000;B2:B5;A2:A5) The function returns the name of that employee from column A whose salary is just less than 30000 in column B. Brigitte
=LOOKUP("J";B2:B5;A2:A5) The function returns the salary of that employee from column B whose is just name starts with an alphabet before the "J" letter in column A, which here will be "Hans" 21682
=LOOKUP(20000;B2:B5;A2:A5) The function returns an #N/A error since there is no value in column B less than 20000. #N/A

=LOOKUP(A1;D1:D100;F1:F100) searches the corresponding cell in range D1:D100 for the number you entered in A1. For the instance found, the index is determined, for example, the 12th cell in this range. Then, the contents of the 12th cell are returned as the value of the function (in the result vector).

Related LibreOffice functions:

HLOOKUP

INDEX

MATCH

OFFSET

VLOOKUP

ODF standard:

Section 6.14.8, part 2

Equivalent Excel functions:

LOOKUP