Formualizer Docs
ReferenceFunctionsLookup Functions

MATCH

MATCH: Returns the relative position of a lookup value in a one-dimensional array.

Summary

MATCH supports exact and approximate modes and returns a 1-based position.

Remarks

  • match_type defaults to 1 (approximate, ascending).
  • match_type=0 performs exact matching and supports *, ?, and ~ wildcards for text.
  • match_type=1 looks for the largest value less than or equal to the lookup value.
  • match_type=-1 looks for the smallest value greater than or equal to the lookup value.
  • Approximate modes require sorted data; unsorted data returns #N/A.
  • If no match is found, returns #N/A.

Examples

Exact text match
Grid
CellValue
Formula
=
Result
Not evaluated yet.
Expected
2
Approximate numeric match
Grid
CellValue
Formula
=
Result
Not evaluated yet.
Expected
2

FAQ

Why does MATCH with match_type 1 or -1 return #N/A on unsorted data?

Approximate modes assume ordered lookup data; this implementation treats detected unsorted inputs as no valid match and returns #N/A.

When are wildcards interpreted in MATCH?

Wildcard patterns (*, ?, ~ escapes) are only applied in exact mode (match_type=0) for text lookup values.

Runtime metadata

Category

Lookup

Signature

MATCH(arg1: Any, arg2: Any (Range), arg3?: Number)

Arity

min 2, max 3

Arguments

arg1

Any · Scalar

arg2

Any · Range

arg3optionaldefault

Number · Scalar · coercion NumberLenientText

Caps

PURELOOKUP

Source

crates/formualizer-eval/src/builtins/lookup/core.rs

HSTACK

HSTACK: Concatenates arrays horizontally into a single spilled array.

PIVOTBY

PIVOTBY: Builds a pivot-style summary matrix from row fields, column fields, and values.

On this page

SummaryRemarksExamplesRelated functionsFAQWhy does MATCH with match_type 1 or -1 return #N/A on unsorted data?When are wildcards interpreted in MATCH?Runtime metadata