VLOOKUP, HLOOKUP, and XLOOKUP in LibreOffice Calc

LibreOffice Calc provides powerful lookup capabilities for searching and retrieving data across spreadsheets, fully supporting traditional functions like VLOOKUP and HLOOKUP alongside modern solutions for XLOOKUP. This article explains how each of these lookup functions works in Calc, the exact syntax required, and the best alternatives to achieve modern lookup behavior across all versions of the software.

VLOOKUP (Vertical Lookup)

VLOOKUP searches down the leftmost column of a specified cell range to find a matching value and returns data from a specified column in the same row.

Syntax

=VLOOKUP(SearchCriterion, Array, Index, SortOrder)

• SearchCriterion: The value to search for in the first column.
• Array: The table range containing the data (e.g., A2:D50).
• Index: The column number in the range from which to return data (where the first column is 1).
• SortOrder (Optional): Enter 0 (or FALSE) for an exact match, or 1 (or TRUE) for an approximate match on a sorted range. Always use 0 for unsorted data to prevent incorrect results.

Example

=VLOOKUP("E101", A2:D50, 3, 0)

This formula searches for the employee ID “E101” in column A and returns the corresponding value from column C (the 3rd column in range A2:D50).

HLOOKUP (Horizontal Lookup)

HLOOKUP functions identically to VLOOKUP, but it operates horizontally. It searches across the top row of a dataset and returns a value from a specified row within the same column.

Syntax

=HLOOKUP(SearchCriterion, Array, Index, SortOrder)

• SearchCriterion: The value to locate in the top row.
• Array: The table range (e.g., A1:Z10).
• Index: The row number in the range from which to return data (where the top row is 1).
• SortOrder (Optional): 0 (or FALSE) for an exact match; 1 (or TRUE) for an approximate match.

Example

=HLOOKUP("Q1_Sales", A1:Z10, 4, 0)

This formula finds “Q1_Sales” in row 1 and returns the value from row 4 of that same column.

XLOOKUP in LibreOffice Calc

Starting with LibreOffice 24.8, native support for XLOOKUP is included directly in Calc, bringing parity with modern spreadsheet software.

Syntax

=XLOOKUP(SearchCriterion, LookupArray, ReturnArray, [IfNotFound], [MatchMode], [SearchMode])

• SearchCriterion: The value to look up.
• LookupArray: The single column or row to search.
• ReturnArray: The single column or row containing the value to return.
• IfNotFound (Optional): Text or value to display if no match is found (e.g., "Not Found").
• MatchMode (Optional): 0 for exact match (default), -1 for exact or next smaller, 1 for exact or next larger, 2 for wildcard match.
• SearchMode (Optional): 1 to search from first to last (default), -1 to search from last to first.

Example

=XLOOKUP("E101", A2:A50, D2:D50, "Not Found")

The XLOOKUP Equivalent: INDEX and MATCH

For versions of LibreOffice Calc prior to 24.8, or for universal compatibility across older spreadsheet tools, the combination of INDEX and MATCH serves as the primary equivalent to XLOOKUP. This method overcomes the limitations of VLOOKUP by allowing lookups to the left and eliminating the risk of broken formulas when columns are inserted.

Formula Structure

=INDEX(ReturnRange, MATCH(SearchCriterion, LookupRange, 0))

• ReturnRange: The column or row containing the result you want to retrieve.
• MATCH: Finds the relative position of the search value within the lookup range.
• 0: Forces MATCH to find an exact match.

Example (Left Lookup)

=INDEX(A2:A50, MATCH("Widget Pro", B2:B50, 0))

This formula searches for “Widget Pro” in column B and returns the corresponding item code from column A, a task that VLOOKUP cannot natively perform.