Exact vs Approximate Lookup in LibreOffice Calc

In LibreOffice Calc, retrieving data from spreadsheets relies on lookup functions configured for either exact or approximate matches. Exact matching searches for an identical value and returns an error if no match is found, whereas approximate matching locates the nearest value in a sorted dataset. This guide explains which LibreOffice Calc functions support both matching modes, how to configure their arguments, and which functions are restricted to approximate matching.

Functions Supporting Both Exact and Approximate Matches

1. VLOOKUP and HLOOKUP

VLOOKUP (vertical search) and HLOOKUP (horizontal search) use a dedicated boolean parameter to switch between exact and approximate matching.

• Syntax:
  • =VLOOKUP(SearchCriterion, Array, Index, SortedRangeLookup)
  • =HLOOKUP(SearchCriterion, Array, Index, SortedRangeLookup)
• Exact Match (SortedRangeLookup = 0 or FALSE): Calc searches the first column or row for an exact match. The lookup range does not need to be sorted.
• Approximate Match (SortedRangeLookup = 1, TRUE, or omitted): Calc searches for an exact match; if not found, it returns the largest value less than or equal to the SearchCriterion. The lookup range must be sorted in ascending order.

2. MATCH

The MATCH function returns the relative position of an item in a single row or column. It supports exact matching and two directions of approximate matching via the Type argument.

• Syntax: =MATCH(SearchCriterion, LookupArray, Type)
• Exact Match (Type = 0): Finds the exact match. The array does not need to be sorted.
• Approximate Match (Ascending, Type = 1 or omitted): Finds the largest value less than or equal to SearchCriterion. The array must be sorted in ascending order.
• Approximate Match (Descending, Type = -1): Finds the smallest value greater than or equal to SearchCriterion. The array must be sorted in descending order.

3. XLOOKUP

Available in modern versions of LibreOffice Calc, XLOOKUP provides directional and wildcard controls without requiring the dataset to be sorted for basic searches.

• Syntax: =XLOOKUP(SearchCriterion, LookupArray, ReturnArray, IfNotFound, MatchMode, SearchMode)
• Exact Match (MatchMode = 0 or omitted): Finds the exact value.
• Approximate Match (Next Smaller, MatchMode = -1): Finds the exact match, or the next smaller item if no match exists.
• Approximate Match (Next Larger, MatchMode = 1): Finds the exact match, or the next larger item if no match exists.
• Wildcard Match (MatchMode = 2): Matches using standard wildcard characters (*, ?).

Functions Supporting Only Approximate Matches

LOOKUP

The standard LOOKUP function is legacy-compatible and only performs approximate matching.

• Syntax:
  • =LOOKUP(SearchCriterion, SearchVector, ResultVector)
  • =LOOKUP(SearchCriterion, Array)
• Behavior: LOOKUP always assumes the search data is sorted in ascending order. It does not provide a parameter for exact matching. If an exact match is missing, it automatically falls back to the largest value that is less than or equal to the search criterion.

Summary of Parameter Values