How to use MATCH in Excel
Returns the position of a value in a list.
Syntax
lookup_valueThe value to findlookup_arrayThe list to search[match_type]0 for an exact match optionalExample
Here’s MATCH on a small table of orders. The highlighted cells are the ones the formula reads.
=MATCH(1005,A2:A9,0)Edit this formula live Change the ranges and watch the answer update. Free, no sign-up.
Common mistakes with MATCH
Use 0 for an exact match
MATCH defaults to approximate match (1), which needs sorted data. Almost always write 0 as the third argument.
It returns a position, not a value
MATCH tells you which row; pair it with INDEX to get the value from that row.
What the error messages mean
Type these slips into Gridwise and this is the feedback you get:
Leaving off the closing bracket
=MATCH(1005,A2:A9,0
Parentheses don’t match. You have 1 opening and 0 closing parentheses. Every ( needs a ).
Questions
What does the MATCH function do in Excel?
Returns the position of a value in a list. The syntax is MATCH(lookup_value, lookup_array, [match_type]).
Why is my MATCH formula giving the wrong answer?
Use 0 for an exact match: MATCH defaults to approximate match (1), which needs sorted data. Almost always write 0 as the third argument. It returns a position, not a value: MATCH tells you which row; pair it with INDEX to get the value from that row.
Does MATCH work in Google Sheets?
Most Excel functions, including the core ones on this page, work the same way in Google Sheets. A few newer functions arrived in Sheets later or behave slightly differently, so test it on your own data.