vLookUp(v, array, n, [type])

vLookUp(v, array, n, [type], [startIndex], [occurrence])

2.1 Parameters

• v — value to search for in the leftmost column.
• array — a 2‑D array or range; the first column is searched.
• n — column index (within the array) from which the result is returned.
• type — search mode (exact, approximate, pattern, regex, sorted, etc.).
• startIndex — starting position for the search. Examples:
  • 1 = first cell in the searched range
  • 2 = second cell
  • -1 = last cell
  • -2 = second‑to‑last cell
  Any non‑zero integer can be used to specify where the search should start or end.
• occurrence — which occurrence of the matching value to use. Examples:
  • 1 = first match (top‑down)
  • 2 = second match (top‑down)
  • -1 = last match (bottom‑up)
  • -2 = second‑to‑last match (bottom‑up)
  Any non‑zero integer can be used to specify which occurrence (counted from startIndex) should be returned.

If type is omitted, it defaults to 1.

3.1 Basic Types

• 0 — linear search for an exact match; v may be a pattern using ? (any single character) and * (any string, including empty). To search for literal ? or *, prefix them with ~.
• 1 — if no exact match is found, returns the largest value that is not greater than v; no pattern matching; the searched range must be sorted ascending.
• -1 — if no exact match is found, returns the smallest value that is not smaller than v; no pattern matching; the searched range must be sorted descending.

3.2 SEARCH:: Flags

• SEARCH::MatchNotGreater (2) — largest value ≤ v.
• SEARCH::MatchNotSmaller (4) — smallest value ≥ v.
• SEARCH::SortAscending (8) — binary search on ascending sorted range.
• SEARCH::SortDescending (16) — binary search on descending sorted range.
• SEARCH::CaseSensitive (128) — case‑sensitive comparison.
• SEARCH::FirstMatch (256) — return the first match.
• SEARCH::LastMatch (512) — return the last match.
• SEARCH::AutoSort (1024) — automatically sort internally during the first update and then use binary search. In this case, startIndex refers to the internally sorted range.
• SEARCH::MixedData (2048) — searched range contains both text and numbers.
• SEARCH::SortIndex (4096) — only with AutoSort; returns index relative to the internally sorted range.
• SEARCH::RegEx (8192) — v is a regular expression.
• SEARCH::IgnorePunctuation (16384) — ignore certain punctuation marks (word sort order).
• SEARCH::NeutralSortOrder (32768) — language‑independent comparison.
• SEARCH::Pattern (65536) — v is a wildcard pattern; cannot be used with fast binary search.

3.3 Equivalences

• 1 = SortAscending + MatchNotGreater
• -1 = SortDescending + MatchNotSmaller
• 0 = plain linear exact match

3.4 Restrictions

• SEARCH::Pattern and SEARCH::RegEx cannot be used with:
  • SEARCH::MatchNotGreater
  • SEARCH::MatchNotSmaller
  • SEARCH::StringSort
  • SEARCH::CaseSensitive
  • SEARCH::SortAscending
  • SEARCH::SortDescending
• SEARCH::MatchNotGreater and SEARCH::MatchNotSmaller cannot be used with SEARCH::FirstMatch or SEARCH::LastMatch.
• If neither SEARCH::FirstMatch nor SEARCH::LastMatch is specified:
  • linear search returns the first match
  • binary search may return any of the existing matches

3.5 Important Notes

If SEARCH::SortAscending or SEARCH::SortDescending is specified, the searched range must not contain formulas that break the sort order during recalculation. In such cases, circular references are not reported for cells other than the result cell.

Binary searches enabled by SortAscending or SortDescending are typically tens or hundreds of times faster than plain linear searches.

3.6 AutoSort

If SEARCH::AutoSort is specified, GS-Calc creates and maintains sort indices for unsorted ranges. These internal indices allow quick binary searches without requiring manual sorting. All indices are created during the first update and then updated as needed.

The speed gain depends on how many vLookUp() formulas are present, how often ranges are reused, and how large the searched ranges are. For very large worksheets, performance can improve by hundreds of times.

SEARCH::AutoSort must be used together with SEARCH::SortAscending or SEARCH::SortDescending. If the data is already partially sorted, use the sort order flag that best matches that partial sorting.

3.7 MixedData

If SEARCH::MixedData is specified, the searched range is assumed to contain both numeric and text cells:

• If v is text, numeric values are internally converted to text before comparison.
• If v is numeric, text strings representing numbers are converted to numbers.

If sorting flags are used with MixedData, the range must be sorted using a mixed text/numeric method. With AutoSort, GS-Calc handles this internally, but it may significantly slow down sorting. Otherwise, use the options in Tools → Sort Cell Range.

3.8 Numeric Codes

For workbooks containing extremely large numbers of vLookUp() functions, you can use the numeric sum of flags instead of individual names to specify the type argument.

If no match is found, vLookUp() returns the #N/A! error value.

=vLookUp(2, {1, 2, 3; "a", "b", "c"}, 2, 0) → "b"

=vLookUp(2.5, {1, 2, 3; "a", "b", "c"}, 2, -1) → "c"

=vLookUp(2.5, {1, 2, 3; "a", "b", "c"}, 2, 1) → "b"

=vLookUp("*bc??", {"abc", "abcde", "ac"; 1, 2, 3}, 2, 0) → 2

=vLookUp(2.5, sheet1!b5:d10000, 2, SEARCH::SortAscending + SEARCH::MatchNotGreater)

=vLookUp("bc\\d", {"abc", 1; "abcde", 2; "abc10", 3}, 2, SEARCH::RegEx) → 3

You can insert the vLookUp() function with two clicks using:

Insert → VLOOKUP()

The “Insert VLOOKUP()” dialog determines optimal parameters, presets all options, and automatically creates the formula.

The source range 16‑element list is stored globally in the settings file. Subsequent source ranges are added in a circular manner using:

Copy as Location (menu command)

Enter (toolbar button