MATCH Function in Excel, Google Sheets, Airtable & Notion
MATCH searches a single row or column and returns the position of the value, not the value itself: MATCH("Cherry", {"Apple","Banana","Cherry"}, 0) is 3. Use 0 as the last argument for an exact match.
Syntax in each app
| App | Support | Syntax & notes |
|---|---|---|
| Excel | ✔ Built in | MATCH(lookup_value, lookup_array, [match_type])Excel 365 also has XMATCH, which matches exactly by default. |
| Google Sheets | ✔ Built in | MATCH(search_key, range, [search_type]) |
| Airtable | ✘ Not available | — Airtable has no MATCH and formulas cannot search other records. Use linked records and lookup fields to connect data; FIND() only searches inside a text string. |
| Notion | ≈ Workaround | list.findIndex(current == value) + 1findIndex is 0-based and returns −1 when nothing matches (so the +1 version gives 0 instead of #N/A). Comparison is case-sensitive. |
| Excel | =MATCH("Cherry", A2:A6, 0) | open excel.new ↗ |
|---|---|---|
| Google Sheets | =MATCH("Cherry", A2:A6, 0) | open sheets.new ↗ |
| Airtable | ✘ No equivalent. Airtable has no MATCH and formulas cannot search other records. Use linked records and lookup fields to connect data; FIND() only searches inside a text string. | |
| Notion workaround | ["Apple", "Banana", "Cherry", "Grape", "Mango"].findIndex(current == "Cherry") + 1 | |
Excel & Sheets use cell ranges. Paste this sample data into cell A1 of a blank sheet, then paste the formula into any empty cell.
Item Apple Banana Cherry Grape Mango
Notion: exact matches use findIndex; for a numeric value with match type 1 or −1 the formula counts how many sorted items are ≤ (or ≥) the value, which is the same position. Text with match type 1 or −1 falls back to an exact match.
Arguments
| Argument | What it means |
|---|---|
lookup_value | The value to find. With match_type 0, text can use * and ? wildcards. |
lookup_array | A single row or column. |
[match_type] optional | 0 = exact. 1 (the default!) = largest value ≤ lookup_value, list must be sorted ascending. −1 = smallest value ≥ lookup_value, list must be sorted descending. |
Examples
| Formula | Result | Why | Copy |
|---|---|---|---|
=MATCH("Cherry", {"Apple","Banana","Cherry"}, 0) | 3 | Exact match: the position, not the value. | |
=MATCH("gr*", A2:A6, 0) | 4 | Tester data: wildcards work with match_type 0, case is ignored. | |
=MATCH(75, {0,50,70,90}, 1) | 3 | Ascending list: 70 is the largest value not above 75. | |
=MATCH(65, {100,80,60,40}, -1) | 2 | Descending list: 80 is the smallest value not below 65. |
Examples use Excel / Google Sheets syntax unless noted.
How MATCH works
MATCH tells you where something is. On its own that is useful for checking whether a value exists (=ISNUMBER(MATCH(E2, A:A, 0))), but its real job is feeding a position into INDEX: INDEX + MATCH is the classic flexible lookup.
The trap is the default. If you leave out match_type, MATCH uses 1, an approximate match that assumes the list is sorted ascending and returns the position of the largest value that is less than or equal to what you are looking for. On unsorted data it returns a plausible-looking but wrong position. Type 0 for exact matches; use 1 only for sorted thresholds (tax bands, shipping tiers) and −1 only for lists sorted in descending order. When nothing qualifies, MATCH returns #N/A.
Text matching ignores case, and with match_type 0 you can use * (any characters) and ? (one character); put ~ before a real asterisk. Airtable has no equivalent. In Notion, list.findIndex(current == value) gives the 0-based position and returns −1 instead of an error when the value is missing.
Frequently Asked Questions
- Why does MATCH return the wrong position?
- You probably left out the third argument, so it did an approximate match on unsorted data. Add
0. - How do I check if a value exists in a list?
=ISNUMBER(MATCH(E2, A2:A100, 0))returns TRUE or FALSE. COUNTIF(range, value)>0 works too.