Documentation/Calc Functions/HLOOKUP

From The Document Foundation Wiki
Jump to: navigation, search

Function name:





Searches for a value and reference to the cells below the selected area. This function verifies if the first row of an array contains a certain value. The function returns then the value in a row of the array, named in the Index, in the same column.


HLOOKUP(SearchCriterion; Array; Index[; SortedRangeLookup])


Returns the value of the indicated in the same column of the row named by Index by looking for SearchCriterion in the first row of the Array.


SearchCriterion is the value or a reference to the cell containing that value which is searched for in the first row of the array. If none is found the largest entry less than SearchCriterion is taken as a match. From a sequence of identical values <= SearchCriterion the last entry is taken.

Array is the reference, which is to comprise at least as many columns as the number passed in the Index.

Index is the number of the row in the array or a reference to the cell containing that number that contains the value to be returned. The first row has the number 1.

SortedRangeLookup is a logical value (TRUE/1 or FALSE/0) or a reference to the cell containing that value which indicates whether the first row in the array contains range boundaries instead of plain values. If omitted, the function uses TRUE as the default value. In this mode, the lookup returns the value in the row with the first row having a value equal to or less than SearchCriterion. E.g., it could contain dates when some tax value had been changed, and so the values represent starting dates of a period when a specific tax value was effective. Thus, searching for a date that is absent in the first array row, but falls between some existing boundary dates, would give the lower of them, allowing to find out the data being effective to the searched date. Enter the Boolean value FALSE or zero if the first row is not a range boundary list. When this parameter is TRUE or not given, the first row in the array must be sorted in ascending order. Sorted rows can be searched much faster and the function always returns a value, even if the search value was not matched exactly if it is greater than the lowest value of the sorted list. In unsorted lists, the search value must be matched exactly. Otherwise the function will return #N/A with message: Error: Value Not Available.

If Array is not sorted, the result is undetermined and implementation-dependent. In most cases it will be arbitrary and just plain wrong due to binary search algorithms.

If its value is FALSE and SearchCriterion is text then wildcard characters like question mark (?) (matches any single character) and asterisk (*) (matches any sequence of characters) are allowed for SearchCriterion. Tilde (~) must be added before the character in case you want to find the question mark or asterisk character itself.

See Help page entitled Handling of Empty Cells.

  • If there is no data less than or equal to SearchCriterion, then the function returns an #N/A Error. If SearchCriterion is of type Text and the value found is of type Number, the #N/A Error is returned.

Additional details:

  • Sorted ascending includes smaller Text values before larger ones (e.g., "A" before "B", and "B" before "BA"), and False before True.
  • If the types are mixed, Numbers are sorted before Text, and Text before Logicals. Evaluators without a separate Logical type may include a Logical as a Number.
  • The values returned may vary depending upon the HOST-USE-REGULAR-EXPRESSIONS or HOST-USE-WILDCARDS or HOST-SEARCH-CRITERIA-MUST-APPLY-TO-WHOLE-CELL properties. You can know more about it in Host-Defined Behaviors.
  • The search supports wildcards or regular expressions. With regular expressions enabled, you can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every character with a "\" character, or enclose the text into \Q...\E. You can switch the automatic evaluation of wildcards or regular expression on and off in Tools - Options - LibreOffice Calc - Calculate.

When using functions where one or more arguments are search criteria strings that represents a regular expression, the first attempt is to convert the string criteria to numbers. For example, ".0" will convert to 0.0 and so on. If successful, the match will not be a regular expression match but a numeric match. However, when switching to a locale where the decimal separator is not the dot makes the regular expression conversion work. To force the evaluation of the regular expression instead of a numeric expression, use some expression that can not be misread as numeric, such as ".[0]" or ".\0" or "(?i).0".


1 Apples Lemons Pears Bananas
2 3 7 5 6
3 5 5 No Data 7
4 4 6 4 5
5 2.5 4 3 4
Formula Description Returns
=HLOOKUP("Apples";A1:D5;5;TRUE) The function looks up for "Apples" and gives the value corresponding to row 5 using SortedRangeLookup within the same column. 2.5
=HLOOKUP("Z",A1:D5,3,TRUE) The function looks up as "Z" in row 2 and gives the value from row 3 using SortedRangeLookup within the same column. Here we don't have an exact match for "Z" so the largest value in row 2 that is less than "Z" is used which will be "Lemons" in columns B. 7
=HLOOKUP("Lemons";A1:D5;3;TRUE) The function looks up for "Lemons" and gives the value corresponding to row 3 without using SortedRangeLookup within the same column. No Data
=HLOOKUP(5, {1,"a",2;"b","c",3;"d","e","f";"j","k",4;7,6,5}, 5,1) where there are five rows of values in the array constant, each row separated by a semicolon (;) The function looks up the number 5 in the five-row array constant, and returns the value from row 5 in the same (in this case, first) column. Here 7 is found in row 5 and same column as 1 so. 5
=HLOOKUP(0,{1,"a",2 ; "b","c",3 ; "d","e","f" ; "j","k",4 ; 7,6,5},5,1) Here SearchCriterion has no value smaller then itself so the function returns #N/A. #N/A

Related LibreOffice functions:






ODF standard:

Section 6.14.5, part 2

Equivalent Excel functions: