Syntax
MATCH(lookup_value, lookup_array, [match_type])
| Parameter | Description |
|---|---|
| lookup_value | Parameter of the MATCH function. |
| lookup_array | Parameter of the MATCH function. |
| [match_type] | (Optional.) Parameter of the MATCH function. |
Examples
Find position of a value in a list
Formula
=MATCH("Gadget", A2:A10, 0)
Returns: 2 — "Gadget" is the 2nd item in the range A2:A10
Find closest match in sorted data
Formula
=MATCH(75, B2:B20, 1)
Returns: the position of the largest value less than or equal to 75 (data must be sorted ascending)
Case-insensitive search
Formula
=MATCH("widget", A2:A100, 0)
Returns: the position of "Widget" — MATCH is case-insensitive by default
Common Errors
#N/A
The value was not found in the range. Double-check spelling, extra spaces, or whether the value actually exists in the data.
#VALUE!
The match_type argument is invalid. Use 0 for exact match, 1 for less-than, or -1 for greater-than.
Tips
Always use 0 for exact match
The third argument defaults to 1 (approximate), which requires sorted data. For most lookups, use 0.
Wildcard matching
With match_type 0, you can use wildcards: =MATCH("Sm*", A:A, 0) finds the first entry starting with "Sm".
Pair with INDEX
=INDEX(C:C, MATCH(lookup_value, A:A, 0)) is the standard pattern. MATCH finds the row, INDEX returns the value.
Try MATCH in Viztab
Import your data and use MATCH with 370+ other formulas. No signup required.
Open Viztab