Difference between revisions of "Documentation/How Tos/Calc: LOOKUP function"
From Apache OpenOffice Wiki
< Documentation | How Tos
m (→Syntax:) |
m |
||
| (12 intermediate revisions by 6 users not shown) | |||
| Line 1: | Line 1: | ||
| − | __NOTOC__ | + | {{DISPLAYTITLE:LOOKUP function}} |
| + | {{Documentation/CalcFunc SpreadsheetTOC | ||
| + | |ShowPrevNext=block | ||
| + | |PrevPage=Documentation/How_Tos/Calc:_INDIRECT_function | ||
| + | |NextPage=Documentation/How_Tos/Calc:_MATH_function | ||
| + | }}__NOTOC__ | ||
== LOOKUP == | == LOOKUP == | ||
| Line 15: | Line 20: | ||
: Matching is <u>always case-insensitive</u> - the case setting on the '''Tools - Options - OpenOffice.org Calc - Calculate''' dialog does not apply. | : Matching is <u>always case-insensitive</u> - the case setting on the '''Tools - Options - OpenOffice.org Calc - Calculate''' dialog does not apply. | ||
| − | :<i>Advanced | + | :<i>Advanced topics:</i> |
| − | : If [[Documentation/How_Tos/Regular_Expressions_in_Calc|regular expressions]] are enabled on the | + | : If [[Documentation/How_Tos/Regular_Expressions_in_Calc|regular expressions]] are enabled on the {{menu|Tools|Options|OpenOffice Calc|Calculate}} dialog, <tt>'''LOOKUP'''</tt> will find exact matches, treating <tt>'''lookupvalue'''</tt> as a [[Documentation/How_Tos/Regular_Expressions_in_Calc|regular expression]]. This only makes sense, and should only be used, if you expect there to be unique exact matches. |
| − | : If '<i>Search criteria = and <> must apply to whole cells</i>' is enabled on the | + | : If '<i>Search criteria = and <> must apply to whole cells</i>' is enabled on the {{menu|Tools|Options|OpenOffice Calc|Calculate}} dialog, <tt>'''lookupvalue'''</tt> must match the whole text in the cell; if not, it can match just part of the text. |
=== Examples: === | === Examples: === | ||
| Line 49: | Line 54: | ||
: <tt>'''".at"'''</tt> matches both <tt>'''cat'''</tt> and <tt>'''mat'''</tt> if regular expressions are enabled. It also matches <tt>'''matter'''</tt> if whole cell matching is off. It is not possible to predict which of these will be matched, nor which result will be returned. Use regular expressions with care. | : <tt>'''".at"'''</tt> matches both <tt>'''cat'''</tt> and <tt>'''mat'''</tt> if regular expressions are enabled. It also matches <tt>'''matter'''</tt> if whole cell matching is off. It is not possible to predict which of these will be matched, nor which result will be returned. Use regular expressions with care. | ||
| − | === | + | === Issues: === |
| − | + | * The case sensitivity behavior is discussed in {{bug|71000}} | |
| − | ''' | + | * In Excel, any logical values in <tt>'''searchtable'''</tt> must appear after any text values. In Calc, logical values are assumed to have the numerical values <tt>'''0'''</tt> (<tt>'''FALSE'''</tt>) and <tt>'''1'''</tt> (<tt>'''TRUE'''</tt>). |
| − | ''' | + | * The flexibility and power of regular expressions and the option to disable whole cell matching make this function a lot more complex. The safest and easiest way to use this function is to disable regular expressions and enable whole cell matching. |
| − | ''' | + | |
| − | ''' | + | |
| − | ''' | + | |
| − | + | {{SeeAlso|EN| | |
| + | * [[Documentation/How_Tos/Calc: CHOOSE function|CHOOSE]] | ||
| + | * [[Documentation/How_Tos/Calc: HLOOKUP function|HLOOKUP]] | ||
| + | * [[Documentation/How_Tos/Calc: INDEX function|INDEX]] | ||
| + | * [[Documentation/How_Tos/Calc: MATCH function|MATCH]] | ||
| + | * [[Documentation/How_Tos/Calc: OFFSET function|OFFSET]] | ||
| + | * [[Documentation/How_Tos/Calc: VLOOKUP function|VLOOKUP]] | ||
| − | + | * [[Documentation/How_Tos/Calc: PROB function|PROB]] | |
| − | + | * [[Documentation/How_Tos/Regular_Expressions_in_Calc|Regular Expressions in Calc]] | |
| − | + | * [[Documentation/How_Tos/Calc: Spreadsheet functions|Spreadsheet functions]] | |
| − | * | + | |
| − | * | + | * [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]] |
| − | * | + | * [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}} |
| − | + | [[Category: Documentation/Reference/Calc/Spreadsheet functions]] | |
Latest revision as of 11:11, 2 February 2024
- Spreadsheet FunctionsSpreadsheet Lookup functions
- ADDRESSCHOOSEHLOOKUPINDEXINDIRECTLOOKUPMATHOFFSETVLOOKUPSpreadsheet Information functions
- AREASCOLUMNCOLUMNSERRORTYPEINFOROWROWSSHEETSHEETSOther functions
-
LOOKUP
Returns a value from a single-cell-wide table, in a position found by lookup in another table.
Syntax:
LOOKUP(lookupvalue; searchtable; resulttable)
- lookupvalue is a value (number, text or logical value) to look up in the single row or single column range/array searchtable. searchtable must be sorted, with numbers in ascending order appearing before text values in alphabetic order.
- resulttable is a range/array of the same size as searchtable. LOOKUP returns the value in resulttable in the position where the match was found in searchtable.
- LOOKUP decides where in searchtable lookupvalue would appear. If there is an exact match, that is the position found; if there is more than one exact match, the position found is not necessarily the leftmost/ topmost. If there is no exact match, the position just before where lookupvalue would appear is found; the #N/A error results if that position is not in searchtable.
- Matching is always case-insensitive - the case setting on the Tools - Options - OpenOffice.org Calc - Calculate dialog does not apply.
- Advanced topics:
- If regular expressions are enabled on the Tools → Options → OpenOffice Calc → Calculate dialog, LOOKUP will find exact matches, treating lookupvalue as a regular expression. This only makes sense, and should only be used, if you expect there to be unique exact matches.
- If 'Search criteria = and <> must apply to whole cells' is enabled on the Tools → Options → OpenOffice Calc → Calculate dialog, lookupvalue must match the whole text in the cell; if not, it can match just part of the text.
Examples:
In these examples, cells A1, B1, C1, D1, E1 contain 3, 5, cat, mat, matter, and cells A3, B3, C3, D3, E3 contain A, B, C, D, E.
LOOKUP(3; A1:E1; A3:E3)
- returns A. Cell A1 matches 3. The contents of the corresponding cell in A3:E3 are returned.
LOOKUP(7; A1:E1; A3:E3)
- returns B. 7 would appear between 5 and cat, so the position to the left is found.
LOOKUP(1; A1:E1; A3:E3)
- returns #N/A. 1 would appear before 3 in the top row, and there is no position to the left of that in the table.
LOOKUP("CAT"; A1:E1; A3:E3)
- returns C. The difference between lower and upper case is ignored, so CAT matches cat.
LOOKUP("mate"; A1:E1; A3:E3)
- returns D. mate would appear between mat and matter, so the position to the left is found.
Advanced topic: regular expressions
LOOKUP("c.t"; A1:E1; A3:E3)
- returns C if regular expressions are enabled (and #N/A if not). The dot '.' stands for 'any single character' in a regular expression, so c.t matches cat.
LOOKUP(".at"; A1:E1; A3:E3)
- ".at" matches both cat and mat if regular expressions are enabled. It also matches matter if whole cell matching is off. It is not possible to predict which of these will be matched, nor which result will be returned. Use regular expressions with care.
Issues:
- The case sensitivity behavior is discussed in Issue 71000
- In Excel, any logical values in searchtable must appear after any text values. In Calc, logical values are assumed to have the numerical values 0 (FALSE) and 1 (TRUE).
- The flexibility and power of regular expressions and the option to disable whole cell matching make this function a lot more complex. The safest and easiest way to use this function is to disable regular expressions and enable whole cell matching.
See Also
-
