Documentation/Calc Functions/HLOOKUP

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 sorted correctly (as described in the 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 omitted or set to any value other than 0 or FALSE, then the data in the first row of Array is assumed to be ordered and HLOOKUP can produce both exact and inexact (best-fit) 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.

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 leftmost cell of those sharing common content.
 * If Sorted range lookup is TRUE, the first row of the data table should be sorted with numbers in ascending order appearing before text values in alphabetic order. HLOOKUP calculates where in the first row Search criterion would appear in the 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 to the left of where Search criterion would appear in the first row is used.
 * If regular expressions are enabled in the Formulas Wildcards area of the (or  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 (or  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 (or  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 (or  on macOS) dialog has no effect on the operation of HLOOKUP.

Examples:
The following table is a real-world example of an unsorted table, showing data drawn from the periodic table of the chemical elements.

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.

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 East, North, South, and West) 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.

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 (or  on macOS) dialog

Related LibreOffice functions:
INDEX

LOOKUP

MATCH

OFFSET

VLOOKUP

ODF standard:
Section 6.14.5, part 2

Equivalent Excel functions:
HLOOKUP