1. Function Syntax

match(v, array, [options])

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

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

2. Extended Variant: startIndex and occurrence

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.

3. Search Options

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

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

3.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 (range must be a one‑row or one‑column vector)
• SEARCH::SortDescending (16) — binary search, descending order (range must be a one‑row or one‑column vector)
• SEARCH::CaseSensitive (128) — case‑sensitive comparison
• SEARCH::FirstMatch (256) — return first match
• SEARCH::LastMatch (512) — return last match
• SEARCH::AutoSort (1024) — automatically sort the range internally during the first update
• SEARCH::MixedData (2048) — range contains both text and numbers
• SEARCH::SortIndex (4096) — return index relative to internally sorted range (requires AutoSort)
• 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 *); cannot be used with binary searching

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

3.4 Type Equivalents

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

If options is omitted, it defaults to 1.

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

5. Sorting Requirements

If SEARCH::SortAscending or SEARCH::SortDescending is specified, the searched range must 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.

6. AutoSort

If SEARCH::AutoSort is specified, the program creates and maintains internal sort indices for unsorted ranges. This enables fast binary searching without requiring the user to sort the data manually.

Sort indices are created during the first update after opening the workbook and updated as needed. Performance improvements can be dramatic for large worksheets.

SEARCH::AutoSort must be used together with SEARCH::SortAscending or SEARCH::SortDescending.

7. Mixed Data

If SEARCH::MixedData is specified:

• 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.
• If AutoSort is used, internal sorting is performed automatically.

8. Performance Tip

For extremely large numbers of match() calls, you may use the numeric sum of flags instead of individual names for faster evaluation.

9. Error Handling

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

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

11. Insert match() Automatically

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

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