Excel function

MATCH in Excel: find a value's position

MATCH tells you where a value appears inside a range. It returns a position number, which is why it pairs naturally with INDEX.

What MATCH does

MATCH searches for a value inside a one-dimensional range and returns its position. The basic syntax is `=MATCH(lookup_value, lookup_array, [match_type])`.

If B2:B10 contains product codes and A2 contains the code you want, `=MATCH(A2,B2:B10,0)` returns the position of A2 inside B2:B10. If the match is the first item in the range, the result is 1. If it is the fifth item, the result is 5.

MATCH does not return the matching text or price. It returns a number. That number becomes useful when another function, especially INDEX, needs a row or column position.

Arguments explained

`lookup_value` is the value you want to find, such as the code in A2. `lookup_array` is the one-row or one-column range where Excel should search, such as B2:B10.

`match_type` controls the matching behavior. Use `0` for exact match when looking up IDs, names, categories, or codes. Match type `1` and `-1` are approximate modes and require sorted data in the correct order. They are useful in special tiered lookup cases, but exact match is the safe default for beginners.

Because MATCH returns a position inside the lookup array, the selected range matters. If your lookup array starts at B2, position 1 refers to B2, not worksheet row 1.

Practical examples

A beginner example is finding where a name appears in a list. If A2 contains `Maya` and B2:B12 contains student names, `=MATCH(A2,B2:B12,0)` returns Maya's position in that list.

A business example is finding the row of a product so INDEX can return its margin. `=INDEX(D2:D30,MATCH(A2,B2:B30,0))` looks for the product code in A2 within B2:B30, then returns the corresponding margin from D2:D30.

MATCH can also find column positions. If B1:M1 contains month names and A2 contains `June`, `=MATCH(A2,B1:M1,0)` returns the month position inside that header row.

When to use it

Use MATCH when you need a position, especially for INDEX MATCH formulas or dynamic row and column selection. It is also useful for checking whether a value exists in a list.

Do not use MATCH if you need the actual related value and do not need the position. XLOOKUP may be simpler for direct lookup tasks. Do not use approximate match types unless you understand the required sorting and the business meaning of a nearby match.

Practical examples

Exact match position

=MATCH(A2,B2:B12,0)

Finds the exact value from A2 inside B2:B12 and returns its position within that range.

INDEX MATCH lookup

=INDEX(D2:D30,MATCH(A2,B2:B30,0))

MATCH finds the product code position, and INDEX returns the corresponding value from D2:D30.

Recommended Excel functions

MATCHINDEXXLOOKUPVLOOKUPXMATCH

Common mistakes

  • 01Forgetting to use 0 for exact match with IDs, names, or codes.
  • 02Expecting MATCH to return the matching value instead of its position.
  • 03Using approximate match types without sorted data.
  • 04Combining MATCH with INDEX ranges that do not line up.

Practice

Practice this skill with interactive exercises on Calcu.

Use Calcu to practice MATCH in realistic spreadsheet exercises with instant feedback.

Open Calcu

FAQ

What does MATCH return in Excel?

MATCH returns the position of a value inside a lookup array.

What match type should beginners use?

Use 0 for exact match when searching for IDs, names, categories, or codes.

Why is MATCH used with INDEX?

MATCH finds the row or column position, and INDEX uses that position to return the related value.