MATCH
Returns the relative position of a value within a range. Often combined with INDEX for powerful lookups.
MATCH(lookup_value, lookup_array, [match_type])Arguments
lookup_valueThe value you want to findlookup_arrayThe range of cells to searchmatch_type0 for exact match, 1 for less than, -1 for greater than(optional)
=MATCH("Apple",A1:A10,0)Returns 3 if 'Apple' is the 3rd item in the range
=MATCH(100,B1:B20,1)Finds position of largest value <= 100
=INDEX(B:B,MATCH(A1,A:A,0))Classic INDEX/MATCH combination
- •Use 0 for exact match (most common)
- •Match type 1 requires data sorted ascending, -1 requires descending
- •MATCH returns a position number, not the actual value
- •Skipping match_type and assuming it defaults to exact match - it actually defaults to 1 (approximate), which is rarely what you meant
- •Expecting MATCH to hand back the value you searched for. It only ever gives you a position number, nothing else
- •Using match_type 1 or -1 on data that isn't sorted the way it needs to be - MATCH won't complain, it'll just quietly give you the wrong position
What's the difference between MATCH and VLOOKUP?
MATCH only tells you where something is (e.g. it's the 3rd item in the range). VLOOKUP tells you what's next to it. Combine them and you get the best of both: MATCH finds the spot, INDEX grabs the value sitting there.
Why is MATCH giving me the wrong position?
Check match_type first - 1 or -1 (approximate match) needs sorted data, and unsorted data will silently throw off the result. After that, look for extra spaces or a data type mismatch (text vs number) between lookup_value and lookup_array.
Does MATCH support wildcards?
Yes, as long as match_type is set to 0 (exact match). Then * and ? work fine in lookup_value for partial text matches.
Need to translate a formula using MATCH?
Use our translator to convert your complete formula