Formualizer Docs
ReferenceFunctionsLookup Functions

XMATCH

XMATCH: Returns the 1-based position of a value in a one-dimensional lookup array.

Summary

XMATCH extends MATCH with explicit search direction and wildcard mode.

Remarks

  • Defaults: match_mode=0 (exact), search_mode=1 (first-to-last).
  • match_mode: 0 exact, -1 exact-or-next-smaller, 1 exact-or-next-larger, 2 wildcard.
  • search_mode: 1 forward, -1 reverse, 2 ascending binary intent, -2 descending binary intent.
  • lookup_array must be a single row or single column, otherwise returns #VALUE!.
  • Not found returns #N/A.

Examples

Exact match position
Grid
CellValue
Formula
=
Result
Not evaluated yet.
Expected
2
Reverse search finds last duplicate
Grid
CellValue
Formula
=
Result
Not evaluated yet.
Expected
3

FAQ

How do search_mode values affect duplicate matches?

search_mode=1 returns the first qualifying match, while search_mode=-1 scans from the end and returns the last qualifying match.

When do binary-intent search modes (2 or -2) return #N/A?

For approximate modes they require sorted data in the expected direction; unsorted arrays are treated as no valid match and return #N/A.

Runtime metadata

Category

Lookup

Signature

XMATCH(arg1: Any, arg2: Range (Range), arg3?: Number, arg4?…: Number)

Arity

min 2, max variadic

Arguments

arg1

Any · Scalar

arg2by-ref

Range · Range

arg3optionaldefault

Number · Scalar · coercion NumberLenientText

arg4optionaldefault

Number · Scalar · coercion NumberLenientText

Caps

PURELOOKUP

Source

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

XLOOKUP

XLOOKUP: Looks up a value in one array and returns the aligned value from another array.

Math Functions

Generated reference pages for Math functions.

On this page

SummaryRemarksExamplesRelated functionsFAQHow do search_mode values affect duplicate matches?When do binary-intent search modes (2 or -2) return #N/A?Runtime metadata