Excel Functions

Excel MATCH Function

Pinterest LinkedIn Tumblr

Excel MATCH function used to locate the position of a lookup value in a row, column, or table. Often, the INDEX function is combined with the MATCH to retrieve the value at the position returned by MATCH.

Purpose of Excel MATCH Function

Get the position of an item in an array.

Return value

.A number representing a position in lookup_array.

Syntax

=MATCH (lookup_value, lookup_array, [match_type])

Arguments

ArgumentsDescriptionRemarks
lookup_valueThe value to match in lookup_array.Mandatory
lookup_arrayA range of cells or an array reference.Mandatory
[match_type]exact or next smallest (default), 0 = exact match, -1 = exact or next largest.Optional

Usage notes

  • MATCH is not case-sensitive.
  • MATCH returns the #N/A error if no match is found.
  • MATCH only works with text up to 255 characters in length.
  • In the case of duplicates, MATCH returns the first match.
  • If match_type is -1 or 1, the lookup_array must be sorted as noted above.
  • If match_type is 0, the lookup_value can contain the wildcards.
  • The MATCH function is frequently used together with the INDEX function.

Availability

Since 2007 for Windows.
Since 2011 for Mac.

Example

MATCH Function

A simple example of match

Formula used:  =MATCH(E5,$C$4:$C$10,0)

E5: Referring cell
C3:C10: Range
0: Meant for exact match

After executing the above formula the function returns the place in numbers of the referred cell in the table.

Suggested reading: 20 Excel Functions Useful For Finance Professionals

Related Posts

Write A Comment