Exceljet

Quick, clean, and to the point

Excel MATCH Function

Excel MATCH function
Summary 

MATCH is an Excel function used to locate the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching, and wildcards (* ?) for partial matches. Often, the INDEX function is combined with MATCH to retrieve the value at the position returned by MATCH.

Purpose 
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 
  • lookup_value - The value to match in lookup_array.
  • lookup_array - A range of cells or an array reference.
  • match_type - [optional] How to match, specified as -1, 0, or 1. Default is 1.
Usage notes 

Use the MATCH function to get the relative position of an item in an array. Match offers several different matching modes, which makes it more flexible than other lookup functions. Used together with INDEX, MATCH can retrieve the value at the matched position.

Match type information

  • If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. The lookup_array must be sorted in ascending order.
  • If match_type is 0, MATCH finds the first value exactly equal to lookup_value. lookup_array does not need to be sorted.
  • If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. The lookup_array must be sorted in descending order.
  • If match_type is omitted, it is assumed to be 1.
  • Note: All match types will find an exact match.

Notes:

  • Match is not case-sensitive.
  • Match returns the #N/A error if no match is found
  • The argument lookup_array must be placed in descending order: TRUE, FALSE, Z-A,...2, 1, 0, -1, -2,..., and so on.
  • If match_type is 0 and lookup_value is text, the wildcard characters question mark (?) and asterisk (*) can be used in lookup_value
  • If match_type is 0 and lookup_value is text, lookup_value can contain the wildcard characters asterisk (*) and question mark (?). An asterisk matches any sequence of characters; a question mark matches any single character.

Excel Formula Training

Learn Excel formulas and functions with concise, clear videos. Master absolute/relative addresses, dates, text, named ranges, and tools for troubleshooting. Each video comes with a practice worksheet and audio transcript. Instant access and complete 100% guarantee. Start today!

See details.

I have been on your emailing list and your tips have made sense to me so I thought that would take the next step and really try and up skill myself with excel. - Maara
Excel foundational video course
Excel Pivot Table video training course
Excel conditional formatting video course
Excel formulas and functions video training course
Excel Shortcuts Video Course