MATCH

Formulas / MATCH
Locates the position of a specific value within an array
=MATCH(lookup_value, lookup_array, [match_type])
  • lookup_value - the value to match in lookup_array
  • lookup_array - a range of cells or an array reference
  • match_type - [OPTIONAL] set to 1 is for exact or next smallest match, set to 0 is for exact match, set to -1 is for exact or next largest match

Examples

  • =MATCH(D6,B6:B14,0)

    returns 5 because MATCH finds the position of the value in D6 in the lookup_array B6:B14. This command searches for the exact value of D6 in the given range.

  • =MATCH(E2,B3:B11,0)

    returns 4 because MATCH finds the position of E2 in the lookup_array B3:B11. This command searches for the exact value of E2 in the given range.

  • =MATCH(E2,B3:B11,1)

    returns 5 because match type is set to 1, which means MATCH finds an approximate match of the largest value less than or equal to the lookup value E2 in the lookup_array B3:B11.

  • =MATCH("pq*",B3:B11,0)

    returns 6 and uses wildcards to do the lookup. Wildcards are special characters used to match any character in a given string. In this case, the wildcard character '*' will match any character that follows the characters 'pq'.


Summary

The MATCH function is used to search for a value in a range of cells and return the position of that value in the range. It does not return exact matches by default.

  • MATCH supports approximate and exact matching, and you can use wildcards for partial matches.
  • MATCH supports three match modes. The mode MATCH uses is set by the optional match_type argument.
  • MATCH only handles one-dimensional arrays and ranges, which can either be vertical or horizontal.
  • Using MATCH twice in a single formula is possible, and doing this is how you would simultaneously match a row and a column.
  • MATCH can be used in a two-dimensional range by using a row or column as a lookup value that is fed into another MATCH function.

Frequently Asked Questions

What is the MATCH function?
The MATCH function is a Sourcetable function that determines where a lookup value is in a range.
How does the MATCH function work?
The MATCH function can do exact matches and approximate matches and supports wildcards, which allow for partial matching. It returns the position in a lookup_array of the lookup_value as a number.
What are the different match types in MATCH?
  • Match type 1 finds the exact or next smallest match
  • Match type 0 finds the exact match
  • Match type -1 finds the smallest match that equals or is bigger than the lookup value
What is the default match type in MATCH?
By default, the match type in MATCH is set to 1.
Can MATCH be used with INDEX to find a value at a matched position?
Yes, MATCH can be used with INDEX to find a value at a matched position.
Are there any limitations to the MATCH function?
Yes, MATCH only works with one-dimensional ranges or arrays, meaning it only works with a single row or column.

Related Functions

Sourcetable Logo

Work smarter

Al is here to help. Leverage the latest models to
analyze spreadsheets, enrich data, and create reports.

Drop CSV