Match() function

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

The match() function returns the relative position of a number or text 'v' in 'array'.

The 2nd Match() variant uses two additional parameters:
startFrom - positive numbers specify where the searching should start and negative numers specify where it should end. For the first, top-left cell of the searched range 'startFrom'=1, for the 2nd one 'startFrom'=2 etc. For the last, bottom-right cell of the searched range 'startFrom'=-1, for the preceeding cell 'startFrom'=-2 etc.
occurrence - specifies which occurrence of the matching/found value should be used. 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 preceeding match 'occurrence'=-2 etc. The number of occurrences is counted from the 'startFrom' index.

The type 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:

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).
If 'type' is omitted, it's assumed to be 1.

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

Typically, quick binary searches enabled by specifying the SEARCH::SortAscending or SEARCH::SortDescending flags should be significantly (tens/hundreds of times) faster then the plain linear searches.

If SEARCH::AutoSort is specified, GS-Calc will be creating and maintaining sort indices for the referenced searched ranges contaning unsorted data. Thanks to those internally created indices it's possible to use quick binary searches for data that doesn't have to be sorted manually by the user. All sort indices are created during the first update after opening a given workbook and then they are individually updated whenever it's necessary.
The speed gain depends on how many match() formulas are there in your worksheet, how often the same ranges are re-used and how big the searched ranges are. For very large worksheets, this can be even hundreds of times (and more) faster.
The SEARCH::AutoSort flag must be used with SEARCH::SortAscending or SEARCH::SortDescending. If the searched data is already partially sorted, it's recommeneded that you use the sort order flag that matches that partial sorting the best.

If SEARCH::MixedData is specified, the searched range is assumed to contain both numeric and text cells. If the search value is a text string, all values from that range will be (internally) converted to text strings then compared. If it's a number, all text strings that represent numbers will be converted to numbers before the comparison takes place.
If any of the two sorting flags is used along with the SEARCH::MixedData flag, the searched range must be sorted using such a "mixed" text/numeric method. If the SEARCH::AutoSort flag is used, GS-Calc will take care about the correct internal sorting, however this option will cause significant slowing down when used with the "AutoSort" option. Otherwise use the respective options in the Tools > Sort Cell Range dialog box.

If a given workbook is to contain an extremely large number of match() functions, 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() examples:

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

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

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

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

=match("b", sheet1!b5:d10000, SEARCH::SortAscending + SEARCH::MatchNotGreater)

=match(10.5, sheet1!b5:d10000, SEARCH::SortAscending + SEARCH::MatchNotGreater, 3, 2)

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

You can insert the match() function with just two clicks using the Insert > MATCH() command. The displayed "Insert MATCH()" dialog box determines the optimum parameters, pre-set all the options and automatically creates the formula.

The source range 16 element list is a global list created and stored in the settings file. Subsequent source ranges are added in a circular manner by clicking one of the following:

The "Copy as Location" menu command

The "Enter" toolbar button