Syntax, a live playground, a measured match_type gallery (0/1/-1), the sorting and #N/A traps, and a sourced error library: finding a value's position.
Returns the 1-based position of lookup_value in lookup_array. match_type 0 is exact and case-insensitive; 1 and -1 are approximate and require sorted data.
MATCH returns the 1-based position of a value in a range — not the value itself. The match_type argument (0 exact, 1 ascending, -1 descending) is where the surprises live. Every row was measured live in HyperFormula on the sample table. Click any row to load it.
| Expression | Result | Why |
|---|---|---|
| =MATCH("Carol",A2:A6,0) | 3 | Exact match: Carol is the 3rd name |
| =MATCH("Zoe",A2:A6,0) | #N/A | No exact match raises #N/A |
| =MATCH("carol",A2:A6,0) | 3 | Exact match is case-insensitive |
| =MATCH(75000,C2:C6,0) | 3 | Numeric exact match on the salary column |
| =MATCH(75,E2:E6,1) | 3 | Type 1: largest value ≤ 75 is 70 at position 3 |
| =MATCH(95,E2:E6,1) | 5 | Type 1: 95 caps at the top value 90 (position 5) |
| =MATCH(40,E2:E6,1) | #N/A | Type 1: below the smallest value raises #N/A |
| =MATCH(85,F2:F6,-1) | 2 | Type -1: smallest value ≥ 85 is 90 at position 2 |
| Expression | Result | Why |
|---|---|---|
| =MATCH("Carol",A2:A6,0) | 3 | MATCH hands back a row number… |
| =INDEX(C2:C6,MATCH("Carol",A2:A6,0)) | 75000 | …and INDEX turns that position into the value |
Edit the table or type any formula — the engine evaluates instantly. Load a preset to see MATCH find an exact position, walk ascending and descending ranges, and feed INDEX for a full lookup.
8 failure patterns mined from actual user reports on Reddit, Stack Overflow and Super User — each with the authentic symptom, root cause, a fix, and a runnable formula. Not textbook theory.
MATCH is available across modern Excel, Google Sheets and LibreOffice. It has been in Excel since 2003.
| Platform | Support | Notes | Gotcha |
|---|
MATCH is one piece. The full course walks the toolkit end to end with live, measured examples.