LOOKUP in Collabora Office: Find Values in Sorted Vectors and Arrays

=LOOKUP(Lookup; SearchVector [; ResultVector])

Lookup is the required value to find, supplied directly or through a cell. SearchVector is the required data range/array. ResultVector is an optional single-row or single-column result range; values correspond by position, not by matching their own contents.

The search axis must be ascending. In mixed data the Help specifies numbers before text, for example −2, 0, 2, A, B. Unsorted input can produce a plausible but unusable answer rather than an obvious error. Prefer one consistent data type, and sort the whole record set so results stay attached to their keys.

There is no exact-only switch. For ordinary sorted numeric keys, a missing exact value selects the largest key no greater than Lookup. Below the minimum there is no match; above the maximum the last key qualifies. For duplicate exact keys the last matching occurrence is used. Pattern searches and mixed-type/locale comparisons need additional care.

Enter:

In F2 enter 160. In G2 enter =LOOKUP(F2;$A$2:$A$4;$D$2:$D$4). The independently expected result is 5% because 100 is the greatest threshold no greater than 160, and D3 has the same relative position as A3. Columns B and C may contain unrelated data.

Change F2 to 100 for an exact-boundary check: the target remains 5%. With 900 the target is 8%. With −1 the source-based expectation is an unavailable-value error (#N/A), because no threshold qualifies. These are reasoning checks against the sorted-search contract, not observations from a running Collabora instance.

The table describes a single rate for the whole quantity. It does not calculate a progressive/tier-by-tier charge. Make that business meaning explicit before multiplying a quantity by the returned rate.

With two arguments, the result comes from the opposite edge of SearchVector:

• If rows are at least as numerous as columns, search the first column and return from the last column.
• If columns outnumber rows, search the first row and return from the last row.
• A square array therefore uses the first and last columns.

For a vertical A2:B4 table with keys 0, 100, 250 in A and rates 0%, 5%, 8% in B, =LOOKUP(160;A2:B4) has target 5%. For a horizontal H2:J3 table with those keys across row 2 and rates across row 3, =LOOKUP(160;H2:J3) has the same target.

For a one-dimensional A2:A4 list, =LOOKUP(160;A2:A4) returns the selected key 100, since there is no separate return field. Do not pass a broad multi-column table and assume an arbitrary middle column will be returned.

In a separate table put keys 0, 100, 100, 250 in A8:A11 and labels Basic, Standard, Revised, Premium in D8:D11. =LOOKUP(100;A8:A11;D8:D11) has expected result Revised, from the last duplicate.

This can be intentional, but duplicates often hide an input-data problem. Prefer unique thresholds if there should be only one rule per boundary. Keep result vectors the same length as the search axis. The pinned implementation handles short result matrices differently from short cell references: a matrix position outside its size returns #N/A, while a reference result can be offset beyond the endpoint written in the formula and read a cell outside the stated range. Do not rely on either behavior; supply a complete, equally sized result vector.

Collabora Help documents wildcard or regular-expression searching according to the Calc Calculate settings. Those settings are not extra LOOKUP arguments. A string intended literally may be interpreted as a pattern when matching is enabled.

The Help also warns that a criterion that looks numeric can be converted to a number before regular-expression matching, depending on locale. For an auditable lookup, avoid metacharacters in identifiers where possible, record the calculation settings, and test representative text cases in the intended installation. This guide’s worked examples deliberately use numeric thresholds to avoid those dependencies.

A value below the first usable key has no approximate match. A two-dimensional explicit ResultVector is rejected by the pinned evaluator: use a row or column. Check dates stored as text, numbers stored as text, range offsets, duplicate keys, and sorted order before wrapping an error in another function.

Empty search/result cells and mixed-type ranges involve further Calc rules; use explicit clean keys and filled result cells rather than relying on accidental blank handling. Exact error labels, pattern edge cases, and locale collation have not been native-tested here.

Use VLOOKUP or HLOOKUP when you need their explicit table layout and matching arguments. Consult XLOOKUP for a different lookup interface; do not assume its availability in an older deployment.

Reviewed on 19 October 2026 against saved Collabora Office 26.04 Help retrieved on 17 October 2026 and the Collabora co-26.04 source snapshot at commit c1013fad0d38683630b48c01b6d5208b8d81181e. The repository is hosted under LibreOffice/core, but the evidence used here is the pinned Collabora branch snapshot, not a claim about another spreadsheet product. The Help URL marked latest can change. Fresh Help retrieval was unavailable during this review, so Help statements were checked against the saved first-party pages; the pinned implementation files were freshly retrieved and verified.

Examples are independently calculated expectations, not recorded Collabora output. Native Collabora and its GUI were unavailable for this review; no screenshots or application runs are claimed. The first release introducing this function and the last working binary version have not been established. Online, mobile, older releases, localized function names, and round-trip compatibility need separate verification. Formulas use English names and semicolon separators; use the separators configured in your installation.

• Collabora Help: LOOKUP — reviewed 19 October 2026.
• Pinned Collabora lookup evaluator — reviewed 19 October 2026.