Spreadsheet formulas explained, side by side

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

AppSupportSyntax & notes
Excel✔ Built inMATCH(lookup_value, lookup_array, [match_type])
Excel 365 also has XMATCH, which matches exactly by default.
Google Sheets✔ Built inMATCH(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≈ Workaroundlist.findIndex(current == value) + 1
findIndex is 0-based and returns −1 when nothing matches (so the +1 version gives 0 instead of #N/A). Comparison is case-sensitive.
Try MATCH with your own values
Try gr* with match type 0, or Kiwi with match type 1
One value per line, or separate with commas
Result (as Excel & Google Sheets return it)
3
Your formula, ready to paste
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

ArgumentWhat it means
lookup_valueThe value to find. With match_type 0, text can use * and ? wildcards.
lookup_arrayA single row or column.
[match_type] optional0 = exact. 1 (the default!) = largest value ≤ lookup_value, list must be sorted ascending. −1 = smallest value ≥ lookup_value, list must be sorted descending.

Examples

FormulaResultWhyCopy
=MATCH("Cherry", {"Apple","Banana","Cherry"}, 0)3Exact match: the position, not the value.
=MATCH("gr*", A2:A6, 0)4Tester data: wildcards work with match_type 0, case is ignored.
=MATCH(75, {0,50,70,90}, 1)3Ascending list: 70 is the largest value not above 75.
=MATCH(65, {100,80,60,40}, -1)2Descending 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.

Part of the Lookup & Reference functions. Results in the tester follow Excel and Google Sheets behaviour; always double-check important work in your own file.