Returns the position of a value in a range. Type 0 for exact, 1 for largest not exceeding on ascending data, -1 for smallest not below on descending data. Almost always used inside INDEX.

Syntax

=MATCH(lookup_value, lookup_array, [match_type])

• Exact position

  =MATCH("Gizmo",A2:A6,0) → 3

  Exact match position.

• Band position

  =MATCH(72,{0,60,70,80,90},1) → 3

  Approximate on ascending data.

• With INDEX

  =INDEX(B2:B6,MATCH("Gizmo",A2:A6,0)) → 15

  The classic pairing.

• Match type 0 is exact. 1 needs ascending data, -1 needs descending.
• Type 0 supports wildcards: MATCH("Wid*",A2:A6,0).
• Use it to find which column a header is in, then feed INDEX.
• XMATCH adds search-from-end and better defaults.

• #N/A when not found with type 0.
• Omitting the type defaults to 1, which gives wrong positions on unsorted data.
• Not case sensitive.