For business users of Microsoft Excel Free guides and templates
Home >  Excel Help >  Excel Functions >  

Lookup and reference

MATCH Function

Looks up values in a reference or array and returns its relative position as an integer.


by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

(Download the workbooks.)

The MATCH function, working with the INDEX function, offers Excel's most-powerful lookup capability.

Syntax

MATCH(lookup_value, lookup_array, match_type)

  • Managers...
    Charley Kyd can personally help you to apply the Excel methods in this article to your own organization.

    Click here to learn more.
    lookup_value Required. The value you use to find the value you want in a table. You can use the wildcard characters question mark (?) and asterisk (*) in lookup_value when searching text. A question mark matches any single character and an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

  • lookup_array Required. A contiguous range of cells, an array, or an array reference that contain possible lookup values.

  • match_type Optional. The number -1, 0, or 1, which specify the following options:

    • 1: MATCH finds the largest value that is less than or equal to the lookup_value. The lookup_array must be in ascending order: -2, -1, 0, 1, 2, A-Z, FALSE, TRUE

    • 0: MATCH finds the first value that exactly equals the lookup_value. The lookup_array can be in any order. If the lookup_value isn't found, MATCH returns #N/A.

    • -1: MATCH finds the smallest value that is greater than or equal to the lookup_value. The lookup_array must be in descending order: TRUE, FALSE,
      Z-A, 2, 1, 0, -1, -2, etc.

    • If the match_type is omitted, it is assume to be 1.

Applies To

Excel 2003 and above

Remarks

  • MATCH can return results from a closed external workbook.
  • Unlike the SEARCH function, when you use wildcards with MATCH any numbers in the lookup_array will be ignored.

Examples

You can download this example worksheet here, as a zip file with dozens of examples of addtional worksheet functions.

Examples of Excel's MATCH function

Other ExcelUser Information

Other Help

 

Charley's SwipeFile charts


Free Excel Dashboards