# Documentation/Calc Functions/HLOOKUP

TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

## Function name:

HLOOKUP

## Category:

Spreadsheet

## Summary:

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.

## Syntax:

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

## Returns:

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**.

## Arguments:

**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**.

## Examples:

A | B | C | D | |
---|---|---|---|---|

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:

## Equivalent Excel functions:

HLOOKUP