Difference between revisions of "Documentation/How Tos/Calc: HLOOKUP function"

From Apache OpenOffice Wiki
Jump to: navigation, search
(Example:)
 
(18 intermediate revisions by 5 users not shown)
Line 1: Line 1:
__NOTOC__
+
{{Documentation/MasterTOC
 +
|bookid=1234'''
 +
|booktitle=<div style="padding: 8px; font-size: 140%; font-weight: bold; background-color: #9BC0F5;">CALC FUNCTIONS</div>
 +
|ShowParttitle=block|parttitle=[[Documentation/How_Tos/Calc:_Spreadsheet_functions|<div style="font-size: 140%;">Spreadsheet Functions]]
 +
|ShowNextPage=block|NextPage=Documentation/How_Tos/Calc:_INDEX_function
 +
|ShowPrevPage=block|PrevPage=Documentation/How_Tos/Calc:_CHOOSE_function
 +
|ShowPrevPart=block|PrevPart=Documentation/How_Tos/Calc:_Number_Conversion_functions
 +
|ShowNextPart=block|NextPart=Documentation/How_Tos/Calc:_Statistical_functions
 +
|toccontent= <div style="padding: 4px; font-size: 130%; font-weight: hidden; background-color:#DCE9FC;">FUNCTIONS</div>
 +
 
 +
<div style="font-size: 140%; border-style: outset outset outset none; border-color:#DCE9FC;">Spreadsheet Lookup functions</div>
 +
* [[Documentation/How_Tos/Calc:_ADDRESS_function|<div style="font-size: 120%;">Address]]
 +
* [[Documentation/How_Tos/Calc:_CHOOSE_function|<div style="font-size: 120%;">Choose]]
 +
* [[Documentation/How_Tos/Calc:_HLOOKUP_function|<div style="font-size: 120%; border-style: double; border-color:#778899;">Hlookup]]
 +
* [[Documentation/How_Tos/Calc:_INDEX_function|<div style="font-size: 120%;">Index]]
 +
* [[Documentation/How_Tos/Calc:_INDIRECT_function|<div style="font-size: 120%;">Indirect]]
 +
* [[Documentation/How_Tos/Calc:_LOOKUP_function|<div style="font-size: 120%;">Lookup]]
 +
* [[Documentation/How_Tos/Calc:_MATCH_function|<div style="font-size: 120%;">Math]]
 +
* [[Documentation/How_Tos/Calc:_OFFSET_function|<div style="font-size: 120%;">Offset]]
 +
* [[Documentation/How_Tos/Calc:_VLOOKUP_function|<div style="font-size: 120%;">Vlookup]]
 +
 
 +
<div style="font-size: 140%; border-style: outset outset outset none; border-color:#DCE9FC;">Spreadsheet Information functions</div>
 +
* [[Documentation/How_Tos/Calc:_AREAS_function|<div style="font-size: 120%;">Areas]]
 +
* [[Documentation/How_Tos/Calc:_COLUMN_function|<div style="font-size: 120%;">Column]]
 +
* [[Documentation/How_Tos/Calc:_COLUMNS_function|<div style="font-size: 120%;">Columns]]
 +
* [[Documentation/How_Tos/Calc:_ERRORTYPE_function|<div style="font-size: 120%;">Errortype]]
 +
* [[Documentation/How_Tos/Calc:_INFO_function|<div style="font-size: 120%;">Info]]
 +
* [[Documentation/How_Tos/Calc:_ROW_function|<div style="font-size: 120%;">Row]]
 +
* [[Documentation/How_Tos/Calc:_ROWS_function|<div style="font-size: 120%;">Rows]]
 +
* [[Documentation/How_Tos/Calc:_SHEET_function|<div style="font-size: 120%;">Sheet]]
 +
* [[Documentation/How_Tos/Calc:_SHEETS_function|<div style="font-size: 120%;">Sheets]]
 +
 
 +
<div style="font-size: 140%; border-style: outset outset outset none; border-color:#DCE9FC;">Other functions</div>
 +
* [[Documentation/How_Tos/Calc:_DDE_function|<div style="font-size: 120%;">Dde]]
 +
* [[Documentation/How_Tos/Calc:_HYPERLINK_function|<div style="font-size: 120%;">Hyperlink]]
 +
* [[Documentation/How_Tos/Calc:_STYLE_function|<div style="font-size: 120%;">Style]]
 +
}}__TOC__
  
 
== HLOOKUP  ==
 
== HLOOKUP  ==
Line 11: Line 47:
 
: 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).
  
: 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. <tt>'''HLOOKUP'''</tt> decides where in the top row <tt>'''lookupvalue'''</tt> would appear. If there is an exact match, that is the column found; if there is more than one exact match, the column found is <u>not necessarily the leftmost</u>. If there is no exact match, the column 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 column is not in the table.
  
: <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>.
 
  
: If '<i>Search criteria + and <> must apply to whole cells</i>' is enabled on the '''Tools - Options - OpenOffice.org Calc - Calculate''' dialog, <tt>'''lookupvalue'''</tt> must match the whole text in the cell; if not, it can match just part of the text.
+
: If [[Documentation/How_Tos/Regular_Expressions_in_Calc|regular expressions]] are enabled on the '''Tools - Options - OpenOffice.org Calc - Calculate''' dialog, <tt>'''HLOOKUP'''</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 <tt>'''mode'''</tt> is <tt>'''0'''</tt> or <tt>'''FALSE'''</tt>.
 +
 
 +
: If '<i>Search criteria = and <> must apply to whole cells</i>' is enabled on the '''Tools - Options - OpenOffice.org Calc - Calculate''' dialog, <tt>'''lookupvalue'''</tt> must match the whole text in the cell; if not, it can match just part of the text.
  
 
: 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.
Line 25: Line 62:
 
[[Image:Calc_hlookup_example.png|center]]
 
[[Image:Calc_hlookup_example.png|center]]
  
<tt>'''HLOOKUP(3; A1:B5; 2; 0)'''</tt>
+
<tt>'''HLOOKUP(3; A1:E2; 2; 0)'''</tt>
: returns <tt>'''A'''</tt>. Cell A1 in the top row matches <tt>'''3'''</tt>. The top row happens to be sorted, but this is not necessary in mode <tt>'''0'''</tt>.
+
: returns <tt>'''A'''</tt>. Cell A1 in the top row matches <tt>'''3'''</tt>. The contents of the corresponding cell on the 2<sup>nd</sup> row (that is, A2) are returned. The top row happens to be sorted, but this is not necessary in mode <tt>'''0'''</tt>.
  
<tt>'''HLOOKUP(4; A1:B5; 2; 0)'''</tt>
+
<tt>'''HLOOKUP(4; A1:E2; 2; 0)'''</tt>
 
: returns <tt>'''#N/A'''</tt>. There is no match for <tt>'''4'''</tt>.
 
: returns <tt>'''#N/A'''</tt>. There is no match for <tt>'''4'''</tt>.
  
<tt>'''HLOOKUP("CAT"; A1:B5; 2; 0)'''</tt>
+
<tt>'''HLOOKUP("CAT"; A1:E2; 2; 0)'''</tt>
 
: returns <tt>'''C'''</tt>. The difference between lower and upper case is ignored, so <tt>'''CAT'''</tt> matches <tt>'''cat'''</tt>.
 
: returns <tt>'''C'''</tt>. The difference between lower and upper case is ignored, so <tt>'''CAT'''</tt> matches <tt>'''cat'''</tt>.
  
<tt>'''HLOOKUP("m.t"; A1:B5; 2; 0)'''</tt>
+
<tt>'''HLOOKUP("c.t"; A1:E2; 2; 0)'''</tt>
: returns <tt>'''D'''</tt> if regular expressions are enabled (and <tt>'''#N/A'''</tt> if not). The dot '.' stands for 'any single character' in a regular expression, so <tt>'''m.t'''</tt> matches <tt>'''mat'''</tt>.
+
: returns <tt>'''C'''</tt> if regular expressions are enabled (and <tt>'''#N/A'''</tt> if not). The dot '.' stands for 'any single character' in a regular expression, so <tt>'''c.t'''</tt> matches <tt>'''cat'''</tt>.
  
<tt>'''HLOOKUP(".at"; A1:B5; 2; 0)'''</tt>
+
<tt>'''HLOOKUP(".at"; A1:E2; 2; 0)'''</tt>
 
: returns <tt>'''C'''</tt> if regular expressions are enabled. <tt>'''cat'''</tt> matches; so does <tt>'''mat'''</tt>, but the leftmost match is always found in mode <tt>'''0'''</tt>.
 
: returns <tt>'''C'''</tt> if regular expressions are enabled. <tt>'''cat'''</tt> matches; so does <tt>'''mat'''</tt>, but the leftmost match is always found in mode <tt>'''0'''</tt>.
  
<tt>'''HLOOKUP("at"; A1:B5; 2; 0)'''</tt>
+
<tt>'''HLOOKUP("at"; A1:E2; 2; 0)'''</tt>
: returns <tt>'''#N/A'''</tt> if '<i>Search criteria + and <> must apply to whole cells</i>' is enabled and <tt>'''C'''</tt> otherwise. <tt>'''at'''</tt> does not match any whole cell contents, but it matches part of <tt>'''cat'''</tt>.
+
: returns <tt>'''#N/A'''</tt> if '<i>Search criteria = and <> must apply to whole cells</i>' is enabled and <tt>'''C'''</tt> otherwise. <tt>'''at'''</tt> does not match any whole cell contents, but it matches part of <tt>'''cat'''</tt>.
  
<tt>'''HLOOKUP(3; A1:B5; 2; 1)'''</tt>
+
<tt>'''HLOOKUP(3; A1:E2; 2; 1)'''</tt>
:returns <tt>'''A'''</tt>. Cell A1 in the top row matches <tt>'''3'''</tt>. The top row must be sorted in mode <tt>'''1'''</tt>, with text coming after numbers. <tt>'''matter'''</tt> follows <tt>'''mat'''</tt> because although the first letters are the same, it has more letters.
+
:returns <tt>'''A'''</tt>. Cell A1 in the top row matches <tt>'''3'''</tt>. The top row must be sorted in mode <tt>'''1'''</tt>, with text coming after numbers. <tt>'''matter'''</tt> follows <tt>'''mat'''</tt> because although the first letters are the same, <tt>'''matter'''</tt> has more letters.
  
<tt>'''HLOOKUP(4; A1:B5; 2; 1)'''</tt>
+
<tt>'''HLOOKUP(4; A1:E2; 2; 1)'''</tt>
 
: returns <tt>'''A'''</tt>. <tt>'''4'''</tt> would appear between <tt>'''3'''</tt> and <tt>'''5'''</tt> in the top row, so the position to the left is found.
 
: returns <tt>'''A'''</tt>. <tt>'''4'''</tt> would appear between <tt>'''3'''</tt> and <tt>'''5'''</tt> in the top row, so the position to the left is found.
  
<tt>'''HLOOKUP(; A1:B5; 2; 1)'''</tt>
+
<tt>'''HLOOKUP(1; A1:E2; 2; 1)'''</tt>
: returns <tt>''''''</tt>.
+
: returns <tt>'''#N/A'''</tt>. <tt>'''1'''</tt> would appear before <tt>'''3'''</tt> in the top row, and there is no position to the left of that in the table.
 +
 
 +
<tt>'''HLOOKUP(7; A1:E2; 2; 1)'''</tt>
 +
: returns <tt>'''B'''</tt>. <tt>'''7'''</tt> would appear between <tt>'''5'''</tt> and <tt>'''cat'''</tt> in the top row, so the position to the left is found.
 +
 
 +
<tt>'''HLOOKUP("apple"; A1:E2; 2; 1)'''</tt>
 +
: returns <tt>'''B'''</tt>. <tt>'''apple'''</tt> would appear between <tt>'''5'''</tt> and <tt>'''cat'''</tt> in the top row, so the position to the left is found.
 +
 
 +
<tt>'''HLOOKUP("mate"; A1:E2; 2; 1)'''</tt>
 +
: returns <tt>'''D'''</tt>. <tt>'''mate'''</tt> would appear between <tt>'''mat'''</tt> and <tt>'''matter'''</tt> in the top row, so the position to the left is found.
 +
 
 +
<tt>'''HLOOKUP("rat"; A1:E2; 2; 1)'''</tt>
 +
: returns <tt>'''E'''</tt>. <tt>'''rat'''</tt> would appear after <tt>'''matter'''</tt> in the top row, so the position to the left is found.
 +
 
 +
<tt>'''HLOOKUP("at"; A1:E2; 2; 1)'''</tt>
 +
: returns <tt>'''B'''</tt>, even if '<i>Search criteria = and <> must apply to whole cells</i>' is disabled. In this case the match between <tt>'''at'''</tt> and <tt>'''cat'''</tt> is ignored in favour of treating <tt>'''at'''</tt> as a word to come between <tt>'''5'''</tt> and <tt>'''cat'''</tt>.
  
<tt>'''HLOOKUP(; A1:B5; 2; 1)'''</tt>
+
=== Issues: ===
: returns <tt>''''''</tt>.
+
*The case sensitivity behaviour is discussed in [http://qa.openoffice.org/issues/show_bug.cgi?id=71000 issue 71000]
  
 +
{{SeeAlso|EN|
 +
* [[Documentation/How_Tos/Calc: CHOOSE function|CHOOSE]]
 +
* [[Documentation/How_Tos/Calc: INDEX function|INDEX]]
 +
* [[Documentation/How_Tos/Calc: LOOKUP function|LOOKUP]]
 +
* [[Documentation/How_Tos/Calc: MATCH function|MATCH]]
 +
* [[Documentation/How_Tos/Calc: OFFSET function|OFFSET]]
 +
* [[Documentation/How_Tos/Calc: VLOOKUP function|VLOOKUP]]
  
<tt>''''''</tt>
+
* [[Documentation/How_Tos/Calc: PROB function|PROB]]
  
=== See also: ===
+
* [[Documentation/How_Tos/Regular_Expressions_in_Calc|Regular Expressions in Calc]]
'''[[Documentation/How_Tos/Calc: CHOOSE function|CHOOSE]]''',
+
'''[[Documentation/How_Tos/Calc: INDEX function|INDEX]]''',
+
'''[[Documentation/How_Tos/Calc: LOOKUP function|LOOKUP]]''',
+
'''[[Documentation/How_Tos/Calc: MATCH function|MATCH]]''',
+
'''[[Documentation/How_Tos/Calc: OFFSET function|OFFSET]]''',
+
'''[[Documentation/How_Tos/Calc: VLOOKUP function|VLOOKUP]]'''
+
  
'''[[Documentation/How_Tos/Regular_Expressions_in_Calc|Regular Expressions in Calc]]'''
+
* [[Documentation/How_Tos/Calc: Spreadsheet functions|Spreadsheet functions]]
  
'''[[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 09:20, 17 July 2018

CALC FUNCTIONS
FUNCTIONS
Spreadsheet Lookup functions
Spreadsheet Information functions
Other functions


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. HLOOKUP decides where in the top row lookupvalue would appear. If there is an exact match, that is the column found; if there is more than one exact match, the column found is not necessarily the leftmost. If there is no exact match, the column to the left of where value would appear in the top row is found; the #N/A error results if that column is not in the table.


If regular expressions are enabled on the Tools - Options - OpenOffice.org Calc - Calculate dialog, HLOOKUP will find exact matches treating lookupvalue as a * regular expression. This only makes sense, and should only be used, if mode is 0 or FALSE.
If 'Search criteria = and <> must apply to whole cells' is enabled on the Tools - Options - OpenOffice.org Calc - Calculate dialog, lookupvalue must match the whole text in the cell; if not, it can match just part of the text.
Matching is always case-insensitive - the case setting on the Tools - Options - OpenOffice.org Calc - Calculate dialog does not apply.

Examples:

In these examples, cells A1, B1, C1, D1, E1 contain 3, 5, cat, mat, matter, and cells A2, B2, C2, D2, E2 contain A, B, C, D, E.

Calc hlookup example.png

HLOOKUP(3; A1:E2; 2; 0)

returns A. Cell A1 in the top row matches 3. The contents of the corresponding cell on the 2nd row (that is, A2) are returned. The top row happens to be sorted, but this is not necessary in mode 0.

HLOOKUP(4; A1:E2; 2; 0)

returns #N/A. There is no match for 4.

HLOOKUP("CAT"; A1:E2; 2; 0)

returns C. The difference between lower and upper case is ignored, so CAT matches cat.

HLOOKUP("c.t"; A1:E2; 2; 0)

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.

HLOOKUP(".at"; A1:E2; 2; 0)

returns C if regular expressions are enabled. cat matches; so does mat, but the leftmost match is always found in mode 0.

HLOOKUP("at"; A1:E2; 2; 0)

returns #N/A if 'Search criteria = and <> must apply to whole cells' is enabled and C otherwise. at does not match any whole cell contents, but it matches part of cat.

HLOOKUP(3; A1:E2; 2; 1)

returns A. Cell A1 in the top row matches 3. The top row must be sorted in mode 1, with text coming after numbers. matter follows mat because although the first letters are the same, matter has more letters.

HLOOKUP(4; A1:E2; 2; 1)

returns A. 4 would appear between 3 and 5 in the top row, so the position to the left is found.

HLOOKUP(1; A1:E2; 2; 1)

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.

HLOOKUP(7; A1:E2; 2; 1)

returns B. 7 would appear between 5 and cat in the top row, so the position to the left is found.

HLOOKUP("apple"; A1:E2; 2; 1)

returns B. apple would appear between 5 and cat in the top row, so the position to the left is found.

HLOOKUP("mate"; A1:E2; 2; 1)

returns D. mate would appear between mat and matter in the top row, so the position to the left is found.

HLOOKUP("rat"; A1:E2; 2; 1)

returns E. rat would appear after matter in the top row, so the position to the left is found.

HLOOKUP("at"; A1:E2; 2; 1)

returns B, even if 'Search criteria = and <> must apply to whole cells' is disabled. In this case the match between at and cat is ignored in favour of treating at as a word to come between 5 and cat.

Issues:

  • The case sensitivity behaviour is discussed in issue 71000



See Also
Retrieved from "https://wiki.openoffice.org/w/index.php?title=Documentation/How_Tos/Calc:_HLOOKUP_function&oldid=243566"
Views
Personal tools
Navigation
Print/export
Tools