XLOOKUP Function (LibreOffice Calc)
Learn the XLOOKUP function in LibreOffice Calc: Extended vertical search and reference to indicated cells.
The XLOOKUP function in LibreOffice Calc is used for this task: Extended vertical search and reference to indicated cells. Return a matching value without specifying a result column index.
This guide explains how to read the formula, reproduce a concrete example, interpret its result and recognize common input mistakes.
Syntax and arguments
XLOOKUP(search_criterion; search_array; result_array; [result_if_not_found]; [match_mode]; [search_mode])
| Argument | Required? | Meaning |
|---|---|---|
Search criterion |
Yes | The value to be found in the first column. |
Search Array |
Yes | The array or range to search. |
Result Array |
Yes | The array or range to return. |
Result if not found |
Optional | If given, return given text, otherwise return #N/A. |
Match Mode |
Optional | 0 - Exact match. Will return #N/A if no match. (default). -1 - Exact match or the next smaller item. 1 - Exact match or the next larger item. 2 - Wildcard match. 3 - Regular expression match. |
Search Mode |
Optional | 1 - Search from the first value (default). -1 - Search from the last value (reverse). 2 - Binary search values sorted in ascending order. -2 - Binary search values sorted in descending order. |
Calc formulas normally use semicolons (;) between arguments in common configurations; the displayed separator may depend on settings or locale.
Worked example
For a product lookup, enter the following into cells F1:G3:
| F (Product) | G (Price) |
|---|---|
| Apple | 5 |
| Pear | 8 |
| Plum | 4 |
=XLOOKUP("Pear";F1:F3;G1:G3)
Expected result: 8.
Why it works: Return a matching value without specifying a result column index. The specified sample table supplies the referenced cells.
How to use it in your own spreadsheet
- Identify the cells or inputs that correspond to the sample values above.
- Enter the formula in an empty cell, changing the references or literal values to match your sheet.
- Compare the output with a manual check on a small known example.
- Extend the formula to a larger dataset only after you understand the result.
Common problems and practical checks
- Use the correct lookup column or return range, and use exact-match options where appropriate.
- Check for duplicate keys, leading spaces and numbers stored as text in lookup data.
- A pasted formula may produce an error when it uses the other program’s argument separator or unsupported options.
Lookup tip: Use exact matching for product codes or identifiers unless you have deliberately sorted your data for approximate matching. Check the behavior when no match exists.
Related functions
Documentation and verification
- LibreOffice Calc Help
- The standalone example was calculated using LibreOffice Calc 25.2.3.2 on October 9, 2026 and its computed result matched the expected value. This does not certify every option, version, or other example.