Difference between revisions of "Documentation/How Tos/Calc: HLOOKUP function"
From Apache OpenOffice Wiki
< Documentation | How Tos
(→See also:) |
(→Syntax:) |
||
| Line 5: | Line 5: | ||
=== Syntax: === | === Syntax: === | ||
| − | <tt>'''HLOOKUP( | + | <tt>'''HLOOKUP(lookupvalue; datatable; rowindex; mode)'''</tt> |
| − | : <tt>''' | + | : <tt>'''lookupvalue'''</tt> is a value (number, text or logical value) to look up in the top row of the range/array <tt>'''datatable'''</tt>. When a value is matched in the top row, <tt>'''HLOOKUP'''</tt> returns the corresponding value (in the same column) in the <tt>'''rowindex'''</tt><sup>th</sup> row of <tt>'''datatable'''</tt>, where <tt>'''rowindex'''</tt> = 1 is the top row. |
: If <tt>'''mode'''</tt> is <tt>'''0'''</tt> or <tt>'''FALSE'''</tt>, the top row of <tt>'''datatable'''</tt> may be unordered, and the first exact match is found (searching left to right). | : If <tt>'''mode'''</tt> is <tt>'''0'''</tt> or <tt>'''FALSE'''</tt>, the top row of <tt>'''datatable'''</tt> may be unordered, and the first exact match is found (searching left to right). | ||
| Line 13: | Line 13: | ||
: If <tt>'''mode'''</tt> is 1 or <tt>'''TRUE'''</tt>, or is omitted, the top row of <tt>'''datatable'''</tt> must be sorted, with numbers in ascending order appearing before text values in alphabetic order. Firstly an exact match is sought; if there is more than one exact match, the one found is <u>not necessarily the leftmost</u>. If there is no exact match, the position to the left of where <tt>'''value'''</tt> would appear in the top row is found; the <tt>'''#N/A'''</tt> error results if that position is not in the table. | : If <tt>'''mode'''</tt> is 1 or <tt>'''TRUE'''</tt>, or is omitted, the top row of <tt>'''datatable'''</tt> must be sorted, with numbers in ascending order appearing before text values in alphabetic order. Firstly an exact match is sought; if there is more than one exact match, the one found is <u>not necessarily the leftmost</u>. If there is no exact match, the position to the left of where <tt>'''value'''</tt> would appear in the top row is found; the <tt>'''#N/A'''</tt> error results if that position is not in the table. | ||
| − | :<tt>'''HLOOKUP'''</tt> will find exact matches treating <tt>''' | + | :<tt>'''HLOOKUP'''</tt> will find exact matches treating <tt>'''lookupvalue'''</tt> as a regular expression, if regular expressions are enabled on the '''Tools - Options - OpenOffice.org Calc - Calculate''' dialog. This only makes sense, and should only be used, if <tt>'''mode'''</tt> is <tt>'''0'''</tt> or <tt>'''FALSE'''</tt>. |
=== Example: === | === Example: === | ||
Revision as of 21:20, 3 May 2008
HLOOKUP
Returns a value from a table row, in the column found by lookup in the first row.
Syntax:
HLOOKUP(lookupvalue; datatable; rowindex; mode)
- lookupvalue is a value (number, text or logical value) to look up in the top row of the range/array datatable. When a value is matched in the top row, HLOOKUP returns the corresponding value (in the same column) in the rowindexth row of datatable, where rowindex = 1 is the top row.
- If mode is 0 or FALSE, the top row of datatable may be unordered, and the first exact match is found (searching left to right).
- If mode is 1 or TRUE, or is omitted, the top row of datatable must be sorted, with numbers in ascending order appearing before text values in alphabetic order. Firstly an exact match is sought; if there is more than one exact match, the one found is not necessarily the leftmost. If there is no exact match, the position to the left of where value would appear in the top row is found; the #N/A error results if that position is not in the table.
- HLOOKUP will find exact matches treating lookupvalue as a regular expression, if regular expressions are enabled on the Tools - Options - OpenOffice.org Calc - Calculate dialog. This only makes sense, and should only be used, if mode is 0 or FALSE.
Example:
HLOOKUP()
- returns .
