MATCH

Return the position of a value within a range, rather than the value itself.

Syntax

MATCH(lookup_value, lookup_array, [match_type])

Argument	Required	Description
`lookup_value`	Required	The value to find.
`lookup_array`	Required	The range to search. A single row or a single column.
`match_type`	Optional	0 for exact. 1 finds the largest value less than or equal to the lookup, and needs ascending order. -1 finds the smallest value greater than or equal, and needs descending order.

Examples

Every example below was executed against VisiGrid engine 0.35.0 — not transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.

Formula	Result	Notes
`=MATCH(102,A2:A4,0)`	`2`	102 is the second entry. The position, not the value.
`=MATCH("Bo",C2:C4,0)`	`2`	Works on text as readily as numbers.
`=MATCH("B*",C2:C4,0)`	`2`	Wildcards work in an exact-match lookup.
`=MATCH(25,A2:A4,1)`	`2`	Ascending data 10/20/30 — the largest value at or below 25 is 20, in position 2.
`=MATCH("102",A2:A2,0)`	`#N/A`	Text and a number are never equal, however alike they look.

It returns a position, not a value

That is the whole function, and it is why MATCH almost always appears wrapped in INDEX: one finds where, the other fetches what.

=INDEX(C2:C4, MATCH(102, A2:A4, 0))

Change the name below and watch the two rows move together — one reports where, the other reports what:

Try Ana, Bo, Cy — then something not in the list.

Name	Value
Ana	10
Bo	20
Cy	30

Look up:	Bo
MATCH position:	2
INDEX of that:	20

Edit the highlighted cell and the formula recalculates — computed by VisiGrid engine 0.35.0, in your browser. Nothing is saved.

MATCH is also useful alone whenever the position is the answer — checking whether a value exists in a list at all, or finding which column a header sits in so a formula can adapt when columns move.

Pass 0 unless you mean otherwise

Like VLOOKUP’s fourth argument, match_type defaults to approximate rather than exact, and the approximate modes carry a sortedness requirement that is easy to forget:

0 — exact. What you want almost always.
1 — largest value at or below the lookup. Requires ascending order.
-1 — smallest value at or above the lookup. Requires descending order.

Used on data sorted the wrong way, 1 and -1 do not error. They return a position, and it is the wrong one.

Excel compatibility

Matches Excel for all three match types and for wildcards. A text lookup value never matches a numeric cell, and a numeric one never matches a text cell — in either direction and whatever the spelling, as in Excel.