Documentation/Calc Functions/HLOOKUP

    From The Document Foundation Wiki
    Other languages:


    Function name:

    HLOOKUP

    Category:

    Spreadsheet

    Summary:

    Performs a horizontal lookup on a specified data table. Compares a given search value with the entries in the first row of the data table and, when a match is found, returns the contents of the cell that lies in the same column as the matched value but in a different row.

    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.

    If your data is set up vertically, with comparison values in the first column of the data table, use VLOOKUP instead of HLOOKUP.

    Syntax:

    HLOOKUP(Search criterion; Array; Index[; Sorted range lookup])

    Returns:

    Returns the contents of the cell that lies in the same column as the matched cell, but in a different row. The data returned may be numeric or non-numeric.

    Arguments:

    Search criterion is a text string (in quotation marks), a number, or a reference to a cell containing one of those types. Search criterion is matched against the contents of the cells in the first row of Array in order to determine the column of interest.

    Array specifies the range of cells occupied by the data table. This range can be 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). Array may also be specified by passing the name of a named range or database range. It is also possible to pass an inline array as the Array argument.

    Index is a positive integer, or a reference to a cell containing that number, that indicates the row containing the data to be returned. The first row has the number 1. Index should not be greater than the number of rows in Array.

    Sorted range lookup is a Boolean / logical value (TRUE or FALSE), or a reference to a cell containing that value, that indicates whether data in the first row of Array is expected to be sorted correctly (as described in the #Additional details: section below).

    • If Sorted range lookup is set to 0 or FALSE, then the data in the first row of Array is assumed to be unordered and HLOOKUP searches for an exact match.
    • If Sorted range lookup is either omitted, or set to TRUE or to any numeric value other than 0, then the data in the first row of Array is assumed to be ordered and HLOOKUP can produce "best-fit" (either exact or inexact) matches.

    The following conditions may arise:

    • If Index is non-numeric, then HLOOKUP reports a #VALUE! error.
    • If Index is a non-integer value, then HLOOKUP truncates it to an integer.
    • If, after any truncation, Index is less than 1 or greater than the number of rows contained in Array, HLOOKUP reports an invalid argument error (Err:502).
    • If Sorted range lookup is TRUE, but the cells in the first row of the data table are not ordered as expected, then the result returned by HLOOKUP 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 Sorted range lookup is FALSE, and no exact match to Search criterion can be found in the data table, then HLOOKUP reports the #N/A (value not available) error.
    • If Sorted range lookup is TRUE, and the Search criterion argument would precede the first cell in the first row of the data table, then HLOOKUP reports the #N/A (value not available) error.

    Additional details:

    Details specific to HLOOKUP function

    • If Sorted range lookup is FALSE and the first row of the table contains multiple cells with the same content, HLOOKUP will only match the first cell (of the first row in Array) of those sharing common content.
    • If Sorted range lookup is TRUE, the first row of the data table is expected to be sorted with numbers in ascending order appearing before text values in alphabetic order. In case the first row of the data table is not really sorted as expected, then the result is unpredictable. HLOOKUP calculates where in the first row Search criterion would appear in the (expected) sorted order.
      • If there is an exact match between Search criterion and one of the entries in the first row, that column is used to determine the return value.
      • If there is no exact match between Search criterion and any of the entries in the first row, the column immediately before where Search criterion would appear in the first row is used.
    • If regular expressions are enabled in the Formulas Wildcards area of the Tools ▸ Options ▸ LibreOffice Calc ▸ Calculate (or LibreOffice ▸ Preferences ▸ LibreOffice Calc ▸ Calculate on macOS) dialog, then HLOOKUP will find exact matches treating Search criterion as a regular expression. This only makes sense if Sorted range lookup is FALSE.
    • If wildcards are enabled in the Formulas Wildcards area of the Tools ▸ Options ▸ LibreOffice Calc ▸ Calculate (or LibreOffice ▸ Preferences ▸ LibreOffice Calc ▸ Calculate on macOS) dialog, then HLOOKUP will find exact matches treating Search criterion as a wildcard expression. This only makes sense if Sorted range lookup is FALSE.
    • If the Search criteria = and <> must apply to whole cells option is ticked in the General Calculations area of the Tools ▸ Options ▸ LibreOffice Calc ▸ Calculate (or LibreOffice ▸ Preferences ▸ LibreOffice Calc ▸ Calculate 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 Tools ▸ Options ▸ LibreOffice Calc ▸ Calculate (or LibreOffice ▸ Preferences ▸ LibreOffice Calc ▸ Calculate on macOS) dialog has no effect on the operation of HLOOKUP. By default, HLOOKUP is not case-sensitive, unless a case-sensitive search is imposed by a specific regular expression.


    General information about Calc's regular expressions

    Note pin.svg

    Note:
    For convenience, the information in this subsection is repeated on all pages describing functions that manipulate regular expressions.

    • 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 Tools ▸ Options ▸ LibreOffice Calc ▸ Calculate and check if 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.

    Calc wildcards
    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" as well, 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 Tools > Options > LibreOffice Calc > Calculate, 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 is a real-world example of an unsorted table, showing data drawn from the periodic table of the chemical elements.

    A B C D E F G H I J K
    1 Element Hydrogen Helium Lithium Beryllium Boron Carbon Nitrogen Oxygen Fluorine Neon
    2 Symbol H He Li Be B C N O F Ne
    3 Atomic Number 1 2 3 4 5 6 7 8 9 10
    4 Relative Atomic Mass 1.008 4.003 6.94 9.012 10.81 12.011 14.007 15.999 18.998 20.18

    The following examples utilize the above data table. In most cases, the Sorted range lookup argument is set to 0 (FALSE) so that HLOOKUP will look for an exact match in the unsorted table.

    Formula Description Returns
    =HLOOKUP("Helium"; $B$1:$K$4; 2; 0)

    The function matches the string "Helium" in the first row of the data table (cell C1) and returns the data from the cell at the intersection of this column and the second row of the table (cell C2). The symbol for helium is returned.

    He
    =HLOOKUP("CARBON"; $B$1:$K$4; 3; 0)

    The function matches the string "CARBON" in the first row of the data table (cell G1) and returns the data from the cell at the intersection of this column and the third row of the table (cell G3). The atomic number for carbon is returned.

    6
    =HLOOKUP("oxygen"; $B$1:$K$4; 4.6; 0)

    The function matches the string "Oxygen" in the first row of the data table (cell I1) and returns the data from the cell at the intersection of this column and the fourth row of the table (cell I4) - the Index value of 4.6 is truncated to 4. The relative atomic mass of oxygen is returned.

    15.999
    =HLOOKUP("Neon"; $B$1:$K$4; 2; TRUE())

    This example shows the confusion that can arise if the Sorted range lookup argument is set or defaulted to TRUE, when the data table is not sorted in the expected order. This formula returns the (unpredictable) text "C", when "Ne" would be expected.

    C
    

    =HLOOKUP("^bo.*"; $B$1:$K$4; 4; 0)
    with regular expressions enabled.

    The function matches the regular expression "^bo.*" in the first row of the data table (cell F1) and returns the data from the cell at the intersection of this column and the fourth row of the table (cell F4). The relative atomic mass of boron is returned.

    10.81

    =HLOOKUP("nit*"; $B$1:$K$4; 4; 0)
    with wildcards enabled.

    The function matches the wildcard expression "nit*" in the first row of the data table (cell H1) and returns the data from the cell at the intersection of this column and the fourth row of the table (cell H4). The relative atomic mass of nitrogen is returned.

    14.007

    The following table shows sales data for a small company. Four columns (headed 1 to 4) show the total sales in each quarter of the year and the name of the top performing salesperson in that quarter. Four columns (headed E_reg, N_reg, S_reg, and W_reg) show the total sales in each region for the year and the name of the top performing salesperson in that region in that year. The data in the first row is sorted in the prescribed order and so the examples that follow do not specify a value for the Sorted range lookup argument, with HLOOKUP applying the default value of TRUE.

    A B C D E F G H I
    1 Category 1 2 3 4 E_reg N_reg S_reg W_reg
    2 Total Sales $27,094 $96,516 $82,485 $48,487 $99,908 $83,535 $46,553 $24,586
    3 Top Salesperson Samuel Aaron Pippa Ollie Lillian George Zara Kristina
    Formula Description Returns
    =HLOOKUP(0; $B$1:$I$3; 2)

    The function tries to match the number 0 in the first row of the data table, but since 0 is less than the first number (1) in the row, the formula reports the #N/A error.

    #N/A
    =HLOOKUP(2.5; $B$1:$I$3; 2)

    The function tries to match the number 2.5 in the first row of the data table. Since 2.5 is greater than 2 and less than 3, HLOOKUP uses the value 2 (located in column C). The formula returns the data from the cell at the intersection of this column and the second row of the table (cell C2). The total sales for the second quarter is returned.

    96516
    =HLOOKUP(3; $B$1:$I$3; 2)

    The function matches the number 3 in the first row of the data table (cell D1) and returns the data from the cell at the intersection of this column and the second row of the table (cell D2). The total sales for the third quarter is returned. Note that the formula =HLOOKUP("3"; $B$1:$I$3; 2) reports the #N/A error.

    82485
    =HLOOKUP(123; $B$1:$I$3; 2)

    The function tries to match the number 123 in the first row of the data table. Since 123 is greater than the (other) numbers present, HLOOKUP uses column E. The formula returns the data from the cell at the intersection of this column and the second row of the table (cell E2). The total sales for the fourth quarter is returned.

    48487
    =HLOOKUP("C_reg"; $B$1:$I$3; 3)

    The function tries to match the string "C_reg" in the first row of the data table, but since "C_reg" is alphabetically before the first text ("E_reg") in the row, the formula reports the #N/A error.

    #N/A
    =HLOOKUP("F_reg"; $B$1:$I$3; 3)

    The function tries to match the string "F_reg" in the first row of the data table. Since "F_reg" is alphabetically after "E_reg" but before "N_reg", HLOOKUP uses the former, located in column F. The formula returns the data from the cell at the intersection of this column and the third row of the table (cell F3). The top salesperson for the "E_reg" region is returned.

    Lillian
    =HLOOKUP("S_reg"; $B$1:$I$3; 3)

    The function matches the string "S_reg" in the first row of the data table (cell H1) and returns the data from the cell at the intersection of this column and the third row of the table (cell H3). The top salesperson for the "S_reg" region is returned.

    Zara
    =HLOOKUP("Zagreb"; $B$1:$I$3; 3)

    The function tries to match the string "Zagreb" in the first row of the data table. Since "Zagreb" is alphabetically after the other strings present, HLOOKUP uses the value that is located latest in the list (column I). The formula returns the data from the cell at the intersection of this column and the third row of the table (cell I3). The top salesperson for the "W_reg" region is returned.

    Kristina

    The final example is included to show that the Array argument can take the form of an inline array. In this case a comma is used as the array column separator, while a semicolon is used as the array row separator. These array separators can be changed via the Separators section of the Tools ▸ Options ▸ LibreOffice Calc ▸ Formula (or LibreOffice ▸ Preferences ▸ LibreOffice Calc ▸ Formula on macOS) dialog

    Formula Description Returns
    =HLOOKUP(5, {1, 2, 3, 4, 5, 6, 7; "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"}; 2)

    The function matches the number 5 in the first row of the array and returns the data from the cell at the intersection of this column and the second row of the array.

    Friday

    Related LibreOffice functions:

    CHOOSE

    INDEX

    LOOKUP

    MATCH

    OFFSET

    VLOOKUP

    ODF standard:

    Section 6.14.5, part 2

    Related (or similar) Excel functions:

    HLOOKUP