address([row], [column])

Creates and returns a string representing a cell address for the specified column and/or row numbers.

If either of the two numbers is omitted, a cell range is returned.

The 'type' argument specifies the reference type for the row/column pair:
1 - absolute,
2 - absolute/relative,
3 - relative/absolute,
4 - relative.
If it's omitted, it's assumed to be 1.

The 'mode' argument specifies the address notation:
0 - the RC notation,
1 - the A1 notation.
The default value of 'mode' is 1.

The optional 'path' argument specifies the (tree) path of the worksheet that the created address refers to.

=address(1, 2,,,) returns $B$1

=address(1, 2) returns $B$1

=address(1,) returns $1:$1

=address(,2) returns $B:$B

=address(1, 2, 4, 0, "folder1\sheet2") returns "folder1\sheet2"!B1

=address(2,,1,0,) returns R2 (the entire 2nd row)

Returns the number of separated ranges that the argument list points to.

Returns the n-th element from the list of the 'v_' arguments.

=choose(2, A1:A10, B1:B10, C1:C10) returns B1:B10

Returns the column number of 'reference'.

=column(B1) returns 2

=column(A1:C5) returns {1, 2, 3}

Returns the number of columns in 'array'.

=columns({1, 2, 3; 4, 5, 6}) returns 3

The FILTER() function enables you to filter data with the number of filters up to the maximum number of columns in a worksheet, sort the results using up to six keys, and pre- or post-filter the data to find compound duplicates consisting of up to 100 keys / columns.

For details, please see the "FILTERING TABLES" help topic.

The "search-range" argument represents a range (or an array) with rows to filter. It can be of any size up to the maximum number of columns in a worksheet, minus one (as the first output column may additionally contain hyperlinks).
If there are any rows found in the search range, FILTER() returns an array with the corresponding number of columns.

The "filters" argument represents a range or an array with filtering expressions for the subsequent columns in the "search-range" range. Thus, the number of cells in "filters" must not exceed the number of columns in the "search-range" range. The n-th cell position in this range/array is a filter expression for the n-th column in the "search-range" data range.
If there is no filter for a given column, the corresponding filter cell can be left empty.
Entering filters in worksheet cells is easy: you can use the Format > Search Filter format style for the desired cells.

The "hyperlink-path" argument represents a worksheet/workbook path that will be used along with cell references if the "include hyperlinks" option is specified (see below).
For example: sheet1, folder1\sheet1, c:\documents\[sample.gsc]sheet1
It can be left empty if both the data and the FILTER() functions are in the same worksheet.

The "options" argument is a number and can be 0 or a combination (a sum from 1 to 7) of the following:
1 - include hyperlinks to the original search-range data rows in the first column of the FILTER() function results;
2 - perform searching for duplicates AFTER all other filters are applied; by default the searches for duplicates are performed first;
4 - as FILTER() is supposed to offer the best possible speed when handling very large tables, by default it doesn't create calculation chains with formulas placed in the "search-range" range; if, for example, some column in the "search-range" consists of formulas and you want to make sure FILTER() is executed after they're updated, add 4 to options. Please note that this will result in significantly slower filtering.

The optional "empty-string" argument represents a number or a string that the FILTER() function is to return if no matching rows are found for the specified filters. If it's omitted, FILTER() returns the "#N/A!" error code in such a case.

The hLookUp() function searches the top row of array for the value v. If a match is found, the function returns the value from the same column and the n‑th row of the array.

1. Extended Variant: startIndex and occurrence

The extended variant adds two parameters:

startIndex — Positive values specify where searching begins (1 = top‑left cell, 2 = next cell, etc.). Negative values specify where searching ends (−1 = bottom‑right cell, −2 = preceding cell, etc.).

occurrence — Specifies which occurrence of the matching value to return. Positive values count matches top‑down (1 = first match, 2 = second match, etc.). Negative values count matches bottom‑up (−1 = last match, −2 = second‑to‑last, etc.). Occurrences are counted relative to startIndex.

2. Search Type

The type argument determines how the search is performed. It may be one of the basic modes (0, 1, −1) or a sum of SEARCH:: flags.

2.1 Basic Search Modes

• 0 — Exact match (linear search)
  Performs a linear search for an exact match. The search value v may contain wildcard patterns:
  • ? — matches any single character
  • * — matches any sequence of characters (including empty)
  To search for literal ? or *, prefix them with a tilde:
  • ~? — literal question mark
  • ~* — literal asterisk
  Pattern matching is only available in mode 0.
• 1 — Largest value ≤ v (ascending)
  If no exact match is found, returns the largest value that is not greater than v. No wildcard matching is performed. The searched range must be sorted in ascending order.
• −1 — Smallest value ≥ v (descending)
  If no exact match is found, returns the smallest value that is not smaller than v. No wildcard matching is performed. The searched range must be sorted in descending order.

2.2 SEARCH:: Flags

Instead of using 0, 1, or −1, you may specify a sum of SEARCH:: flags:

• SEARCH::MatchNotGreater (2) — largest value ≤ v
• SEARCH::MatchNotSmaller (4) — smallest value ≥ v
• SEARCH::SortAscending (8) — binary search, ascending order
• SEARCH::SortDescending (16) — binary search, descending order
• SEARCH::CaseSensitive (128) — case‑sensitive comparison
• SEARCH::FirstMatch (256) — return first match
• SEARCH::LastMatch (512) — return last match
• SEARCH::MixedData (2048) — range contains both text and numbers
• SEARCH::RegEx (8192) — v is a regular expression
• SEARCH::IgnorePunctuation (16384) — ignore certain punctuation marks
• SEARCH::NeutralSortOrder (32768) — language‑neutral comparison
• SEARCH::Pattern (65536) — v is a wildcard pattern (? and *)

2.3 Pattern Matching vs. Regular Expressions

• SEARCH::Pattern
  Uses wildcard matching with ? and *. Cannot be combined with fast binary searching (SortAscending / SortDescending).
• SEARCH::RegEx
  Uses regular expressions. Cannot be combined with:
  • SEARCH::MatchNotGreater
  • SEARCH::MatchNotSmaller
  • SEARCH::StringSort
  • SEARCH::CaseSensitive
  • SEARCH::SortAscending
  • SEARCH::SortDescending

2.4 Type Equivalents

1 = SEARCH::SortAscending + SEARCH::MatchNotGreater
−1 = SEARCH::SortDescending + SEARCH::MatchNotSmaller
0 = (0)

If type is omitted, it defaults to 1.

3. Flag Compatibility Rules

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, while binary search may return any match.

4. Sorting Requirements

If SEARCH::SortAscending or SEARCH::SortDescending is used:

• The searched range must be sorted accordingly.
• The range should not contain formulas that break the sort order during recalculation.
• No circular reference warnings will be reported for cells other than the result cell.

Binary searches are typically tens or hundreds of times faster than linear searches.

5. Mixed Data

If SEARCH::MixedData is specified:

• The range may contain both numbers and text.
• If v is text, all values are converted to text before comparison.
• If v is numeric, text values representing numbers are converted to numbers.
• If sorting flags are used, the range must be sorted using mixed numeric/text ordering.

6. Performance Tip

If a workbook contains many hLookUp() calls, you may use the numeric sum of flags instead of individual names for faster evaluation.

7. Error Handling

If no match is found, hLookUp() returns #N/A!.

8. Examples

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

returns "b"

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

returns "c"

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

returns "b"

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

returns 2

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

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

returns 3

9. Insert hLookUp() Automatically

You can insert hLookUp() with two clicks using Insert → HLOOKUP(). The dialog box determines optimal parameters, presets options, and builds the formula automatically.

10. Source Range List

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

“Copy as Location”

The “Enter” toolbar button

Merges the specified data items (values, cell or range references) horizontally and returns the combined array. Rows without data are left empty.

If 'x' is a reference (a cell or a range of cells) and 'm' and 'n' are numbers, the function returns the reference of the cell from the m-th row and the n-th column of 'x'. If either 'm' or 'n' is omitted or has an explicit value of 0, the function returns a reference respectively of the n-th column or the m-th row.

If 'x' is an array, the function returns respectively a cell value or an array instead of references.

If 'm' or 'n' are arrays or ranges, the function returns an array containing respectively the specified rows or columns of 'x'. If one of these parameters is an array/range, the other must be a non-zero column/row index or #REF! error codes will be returned.

If 'x' is omitted, it's assumed to be a range representing the whole worksheet containing that function. In this case 'm' and 'n' must be greater than 0.

If 'm' or 'n' are out of the valid ranges or if they are 0 which wasn't entered as a constant value, the function returns the #REF! error.

=index(a1:d4, 2, 2) returns b2

=index(, 2, 2) returns b2

=index(a1:b5, 0, 2) returns b1:b5

=index(a1:b5, 3, ) returns a3:b3

=index({1, 3, 5; 2, 4, 6}, 2, 2) returns 4

=index({1, 3, 5; 2, 4, 6},, 2) returns {3; 4}

=index({1, "a"; 2, "b"; 3, "c"}, {1; 2; 3}, 2) returns {"a"; "b"; "c"}

=index({1, "a"; 2, "b"; 3, "c"}, 3, 0) returns {3, "c"}

=index({1, "a"; 2, "b"; 3, "c"}, 0, 2) returns {"a"; "b"; "c"}

=index({1, 3, 5; 2, 4, 6}, {2, 1}, 1) returns {2, 1}

indirect(text)

Converts text to a cell reference. The 'mode' argument specifies the address notation used in 'text': 0 - the 'RC' notation, 1 - the 'A1' notation. The default value of 'mode' is 1.

=indirect("A1", 1) returns (value of) A1

=indirect("A:A", 1) returns the 1st column

=indirect("1:3") returns rows 1 to 3

=indirect("C1", 0) returns the 1st column

lookUp(v, searchArray, resultArray)

The first version searches either (1) the top row of 'array' (if 'array' has more columns than rows) or (2) the leftmost column of 'array' (if 'array' has more rows than columns) and returns a value respectively from either (1) the same column and the last row or (2) the same row and the last column.

The second version of the 'lookUp' function uses the 'searchArray' and 'resultArray' arguments that are one-column or one-row arrays with the same number of cells. After finding 'v' in 'searchArray', the corresponding value from 'resultValue' is returned.

If an exact match can't be found, the largest value not greater than 'v' is returned. On failure, both versions return the #N/A error value.

=lookUp(3, {1, 5, 3, 4; "a", "b", "c", "d"}) returns "c"

=lookUp(3, {1, 5, 3, 4}, {"a", "b", "c", "d"}) returns "c"

match(v, array, [options], [start], [occurrence])

Returns the relative position of a number or text 'v' in 'array'.

The 2nd match() variant uses two additional parameters:
'start' - positive numbers specify from which cell the searching should start and negative numbers specify where it should end. For the first top-left cell of the searched range 'start'=1, for the 2nd one 'start'=2 etc. The bottom-right cell has the index of -1, for the preceding cell 'start'=-2 etc.
'occurrence' - specifies which occurrence of the matching/found value should be returned. Positive values indicate top-down searching and counting. Negative values indicate bottom-up searching and counting.For the first match 'occurrence'=1, for the 2nd 'occurrence'=2 etc. For the last match 'occurrence'=-1, for the preceding match 'occurrence'=-2 etc. The number of occurrences is counted from the 'start' index.

The 'options' argument specifies how the searching procedure should be performed. It can be either one of the three values 0, -1, 1 or a combination (sum) of various 'SEARCH::' flags:

0 - the function searches a given range linearly for an exact match; 'v' can be a search pattern containing '?' (any single character) and '*' (any string, including an empty string); to search for '?' or '*' place a tilde (~) before them,

1 - if an exact match is not found, the function will search for the largest value that is not greater than 'v'; no pattern matching is performed; the searched range must be sorted in the ascending order,

-1 - if an exact match is not found, the function will search for the smallest value than is not smaller than 'v'; no pattern matching is performed; the searched range must be sorted in the descending order,

SEARCH::MatchNotGreater (or 2) - if an exact match is not found, the function will search for the largest value that is not greater than 'v',

SEARCH::MatchNotSmaller (or 4) - if an exact match is not found, the function will search for the smallest value than is not smaller than 'v',

SEARCH::SortAscending (or 8) - perform a fast search for a range that is sorted in the ascending order, the searched range must have the form of a one-column or one-row vector,

SEARCH::SortDescending (or 16) - perform a fast search for a range that is sorted in the descending order, the searched range must have the form of a one-column or one-row vector,

SEARCH::CaseSensitive (or 128) - use case sensitive string comparison,

SEARCH::FirstMatch (or 256) - find the first match,

SEARCH::LastMatch (or 512) - find the last match,

SEARCH::AutoSort (or 1024) - perform background sorting automatically then use the fast binary search. The searched range must have the form of a one-column vector. In this case the 'startFrom' parameter refers to the internally sorted range.

SEARCH::MixedData (or 2048) - the searched range contains both text and numbers.

SEARCH::SortIndex (or 4096) - can only be used with SEARCH::AutoSort; if it's specified, Match() will return the index related to the internally sorted searched range, not to the actual un-sorted range in the worksheet,

SEARCH::RegEx (or 8192) - the 'v' parameter is a regular expression,

SEARCH::IgnorePunctuation (or 16384) - use the word sort order (ignoring certain punctuation marks),

SEARCH::NeutralSortOrder (or 32768) - use neutral, language independent string comparison instead of the default language specific comparison,

SEARCH::Pattern (or 65536) - the "v" parameter is a simple (wildcard) pattern; can't be used with fast binary searching.

-- For more detailed information, please see the respective help topic. --

The '1' value is an equivalent to (SEARCH::SortAscending + SEARCH::MatchNotGreater).

The '-1' value is an equivalent to (SEARCH::SortDescending + SEARCH::MatchNotSmaller).

The '0' value is an equivalent to (0).

The SEARCH::Pattern and SEARCH::RegEx flags can't be used with SEARCH::MatchNotGreater, SEARCH::MatchNotSmaller, SEARCH::StringSort, SEARCH::CaseSensitive, SEARCH::SortAscending, SEARCH::SortDescending.

The SEARCH::MatchNotGreater and SEARCH::MatchNotSmaller flags can not be used with the SEARCH::FirstMatch and SEARCH::LastMatch flags.

If neither SEARCH::FirstMatch nor SEARCH::LastMatch is specified, the linear search returns the first match and the fast search may return any of the existing matches.

If SEARCH::SortAscending or SEARCH::SortDescending is specified, the searched range either must not contain any formulas or the formulas must not break the sort order during the recalculation. Additionally, in such a case, no circular reference will be reported for cells other than the result cell.

If SEARCH::AutoSort is specified, GS-Calc will be creating and maintaining sort indices for the referenced searched ranges containing unsorted data. Thanks to those internally created indices it's possible to use fast binary searches for data that doesn't have to be sorted manually by the user.
The SEARCH::AutoSort flag must be used with SEARCH::SortAscending or SEARCH::SortDescending. If the searched data is already partially sorted, it's recommended that you use the sort order flag that matches that partial sorting the best.

For better performance, when specifying the above options one can use the resulting numeric code instead of the individual option names.

If the 'options' parameter is omitted, it's assumed to be 1.

If the searched value is not found, 'match' returns the #N/A! error value.

=match(2, {1, 2, 3}, 0) returns 2

=match("b", {1, 2, 3; "a", "b", "c"}, 0) returns 5

=match("*bc??", {"abc", "abcde", "ac"}, SEARCH::Pattern) returns 2

=match("bc\d", {"abc", "abcde", "abc10"}, SEARCH::RegEx) returns 3

Returns a reference that starts 'm' rows below or - depending on the sign of 'm' - over the first row of 'source_reference' and 'n' columns after or before the first column of 'source_reference'. The 'width' and 'height' arguments specify the dimensions of the returned reference. If any of the two argument is omitted, it's assumed to have the same value as 'source_reference'.

=offset(A1:B5, 2, 1, 2, 2) returns B3:C4

Creates and returns a pivot table for the 'source' data range.

The 'rows', 'columns' and 'data' parameters represent arrays/ranges containing the respective pivot field indices. The indices are relative to the top-left corner of the 'source' range and the numbering starts from 1. For example:
{1, 5}, {4}, {1}
If some of the indices are incorrect, pivotData returns the #VALUE! error. In the current GS-Calc version the 'columns' array can contain only one element. If the 'data' are omitted, the last specified row field and the default pivot function (set in the 'Options' dialog) will be used (for the last row field).

The 'functions' argument is an array of predefined functions IDs associated with the specified data fields. The possible values are:
PIVOT::Sum
PIVOT::SumPositive
PIVOT::SumNegative
PIVOT::SumSquares
PIVOT::Count
PIVOT::CountPositive
PIVOT::CountNegative
PIVOT::CountZeroes
PIVOT::Mean
PIVOT::Geomean
PIVOT::Harmean
PIVOT::Min
PIVOT::Max
PIVOT::Quartile1
PIVOT::Median
PIVOT::Quartile3
PIVOT::Var
PIVOT::VarP
PIVOT::Stdev
PIVOT::StdevP
PIVOT::Skew
PIVOT::Kurt
PIVOT::Mode

For example: {PIVOT::Sum, PIVOT::Count}

The 'options' parameter can be a sum of the following constants:

PIVOT::ColumnGrandTotals - display column grandtotals in the last column,

PIVOT::RowGrandTotals - include row grandtotals in the last row,

PIVOT::SubTotals - include subtotals (for pivot tables with 2 or more row fields),

PIVOT::ShowZeroes - show zeroes for empty data fields; without this option 'empty' subtotals will be displayed as the #N/A! error code,

PIVOT::RepeatRowFields - if multiple row fields are specified, display all their values, even duplicated ones,

PIVOT::CaseSensitive - if filters are specified, use case sensitive comparison,

PIVOT::SortRowsDescending - present the output rows using the descending sort order,

PIVOT::SortColumnsDescending - present the output columns using the descending sort order,

PIVOT::NoSourceFieldNames - the source range contains no field names in the first row; use the 'Field n.' names instead; for example: {PIVOT::ColumnGrandTotals + PIVOT::RowGrandTotals + PIVOT::SubTotals}

PIVOT::IgnorePunctuation (or 16384) - use the word sort order (ignoring certain punctuation marks) when sorting and filtering,

PIVOT::NeutralSortOrder (or 32768) - use neutral, language independent string comparison instead of the default language specific comparison when sorting and filtering,

PIVOT::Pattern (or 65536) - treat filters that don't start with (>, >=, <, <=, =) operator as simple (wildcard) patterns.

You must specify either the PIVOT::ColumnGrandTotals or at least one column field.

The optional [field, filter] pairs specify the 1-based field index and the condition. Only source data meeting all the specified conditions will be included in the pivot table. Filters can be patterns or plain numbers and text strings with optional leading =,>,<,>=,<= operators.
Conditions can have the following form:
(1) a text string beginning with the =,>,>=,<,<=,<> operators,
(2) a number or a search pattern: a text string containing special characters '?' (any character) or '*' (any string, including an empty string). To search for ? or * place a tilde (~) before them.

=pivotData(C3:H19, {1},,, {PIVOT::Sum}, PIVOT::ColumnGrandTotals)

=pivotData(sheet1!C3:H19, {1}, {2}, {3, 4}, {PIVOT::Sum, PIVOT::Count}, PIVOT::RowGrandTotals + PIVOT::ColumnGrandTotals + PIVOT::SubTotals)

=pivotData(sheet1!B3:F8, {5, 1}, {3},, {PIVOT::Sum}, PIVOT::RowGrandTotals + PIVOT::ColumnGrandTotals + PIVOT::SubTotals, 2, "Jones", 4, ">2010-01-01")

Returns the row number of 'reference'.

=row(B1) returns 1

=row(A1:C5) returns {1; 2; 3; 4; 5}

Returns the number of rows in 'array'.

=rows({1, 2, 3; 4, 5, 6}) returns 2

Receives data from a COM (automation) server identified by its registered 'prog_id' name.

The COM server application can be run on a remote computer or - if the 'machine' argument is omitted - locally.

The optional 'v_' arguments specify the names of the requested properties. Each property can return a variant of the following types:

• VT_EMPTY (empty cells)
• VT_UI1 (error codes)
• VT_R8 (numbers)
• VT_BSTR (text strings)
• SAFEARRAY: an array of any of the above

If there are two or more names specified, the function returns a vector (a one column array) of values. If a given property returns an array, it must be the only 'v_' argument.

=rtd("MSComCtl2.MonthView.2",, "Day")

Sorts 'array' based on the specified 'column/order' pairs and returns the result as a vector of relative rows indices.

The 'column(n)' arguments specified the relative position of the columns that should be used as the sort keys and the 'order(n)' argument specifies the sort order:
0 - descending order,
1 - ascending order.

Use the 'index' function to obtain the final sorted array.

=sort({3; 1; 5; 2; 4}, 1, 1) returns {2; 4; 1; 5; 3}

=sort({3; 1; 5; 2; 4}, 1, 0) returns {3; 5; 1; 4; 2}

=sort({2, "b"; 2, "a"; 3, "d"; 3, "c"; 1, "e"}, 1, 1, 2, 1) returns { 5; 2; 1; 4; 3}

=index({2, "b"; 2, "a"; 3, "d"; 3, "c"; 1, "e"}, {5; 2; 1; 4; 3}, 1) returns {1; 2; 2; 3; 3} where {5; 2; 1; 4; 3} is the result of the above sorting.

Displays a mini-chart in cell (or merged cells). Please online see the full specification.

=SPARKLINE(D17,{"charttype","column";"ymin", 0; "ymax",2000;"firstcolor","#334960"})

Transpose a given array.

=transpose({1, 2, 3; 4, 5, 6}) returns {1, 4; 2, 5; 3, 6}

Executes a function from the specified Python module.

The "type" parameter specifies the type of the returned value:
0 - number
1 - matrix / array of numbers
2 - text string (up to 1024 characters)
3 - string / block of CSV data (of any size) to be pasted without parsing numbers in cells
4 - string / block of CSV data (of any size) to be pasted with parsing numbers in cells
5 - an image (binary data returned as standard Python "memoryview" binary data)

Additionally, you can add to the "type" parameter the following flags :
32 - all ranges/arrays passed from GS-Calc to Python will be saved and passed to Python as blocks/strings of CSV data
64 - same as 32, except that CSV data is created and passed instead of an array, only if there are any non-numeric/text cells within that GS-Calc range/array.

For details, please see the "Python integration" help topic.

Searches 'search' (a one-column array) for unique values.

If 'type' is 0, the 'unique' function returns a vector of indices to the subsequent unique values found in 'vector'.

If 'type' is 1, the 'unique' function returns a vector of unique values found in 'vector'.

If 'type' is 2, the 'unique' function returns a two-column array.
The first column contains unique values, the second one: the number of occurrences of the corresponding value.

If 'type' is omitted, it's assumed to be 1.

=unique({2; 3; 1; 2; 5; 3; 1}, 1) returns {1; 2; 3; 5}

=unique({"a"; "b"; "cd"; "a"; "cd"; "b"; "d"}, 0) returns {1; 2; 3; 5}

=unique({"a"; "b"; "cd"; "a"; "cd"; "b"; "d"}, 2) returns { "a", 2; "b", 2; "cd", 2; "d", 1}

The vLookUp() function searches for a value v in the leftmost column of an array. If a match is found, it returns the value from the same row and the n‑th column of that array.

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.

2. Search Types and SEARCH:: Flags

2.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.

2.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.

2.3 Equivalences

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

2.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

2.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.

2.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.

2.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.

2.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.

3. Examples

=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

4. Inserting vLookUp() via the Menu

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)

Merges the specified data items (values, cell or range references) vertically and returns the combined array. Columns without data are left empty.