tech on the net

MS Excel: MATCH Function (WS)

Learn how to use the Excel MATCH function with syntax and examples.

Description

The Microsoft Excel MATCH function searches for a value in an array and returns the relative position of that item.

Syntax

The syntax for the Microsoft Excel MATCH function is:

MATCH( value, array, [match_type] )

Parameters or Arguments

value is the value to search for in the array.

array is a range of cells that contains the value that you are searching for.

match_type is optional. It the type of match that the function will perform. The possible values are:

match_type Explanation
1 (default) The MATCH function will find the largest value that is less than or equal to value. You should be sure to sort your array in ascending order.

If the match_type parameter is omitted, the MATCH function assumes a match_type of 1.

0 The MATCH function will find the first value that is equal to value. The array can be sorted in any order.
-1 The MATCH function will find the smallest value that is greater than or equal to value. You should be sure to sort your array in descending order.

Note

  • The MATCH function does not distinguish between upper and lowercase when searching for a match.
  • If the MATCH function does not find a match, it will return a #N/A error.
  • If the match_type parameter is 0 and a text value, then you can use wildcards in the value parameter.
Wild card Explanation
* matches any sequence of characters
? matches any single character

Applies To

The MATCH function can be used in the following versions of Microsoft Excel:

  • Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Excel Function

The MATCH function can be used in Microsoft Excel as the following type of function:

  • Worksheet function (WS)

Example (as Worksheet Function)

Let's look at some Excel MATCH function examples and explore how you would use the MATCH function as a worksheet function in Microsoft Excel:

Microsoft Excel

Based on the spreadsheet above, the following Excel MATCH examples would return:

=MATCH(10572,A2:A6,1) would return 3
(it matches on 10571 since the match_type parameter is set to 1)
=MATCH(10572,A2:A6) would return 3
(it matches on 10571 since the match_type parameter has been omitted and will default to 1)
=MATCH(10572,A2:A6,0) would return #N/A
(it doesn't find a match since the match_type parameter is set to 0)
=MATCH(10573,A2:A6,1) would return 4
=MATCH(10573,A2:A6,0) would return 4

Let's look at how we can use wild cards in the MATCH function.

Microsoft Excel

Based on the spreadsheet above, the following Excel MATCH examples would return:

=MATCH("A?ples", A2:A6, 0) would return 1
=MATCH("O*s", A2:A6, 0) would return 2
=MATCH("O?s", A2:A6, 0) would return #N/A

Frequently Asked Questions

Question: In Microsoft Excel, I tried this MATCH formula but it did not work:

=IF(MATCH(B94,Overview!D$54:D$96),"FS","Bulk")

I was hoping for an easier formula than this:

=IF(OR(B94=Overview!D$54,B94=Overview!D55,B94=Overview!D56,  {etc thru D96} ),"FS","Bulk")

Answer: When you are using the MATCH function, you need to be aware of a few things.

First, you need to consider whether your array is sorted in a particular order (ie: ascending order, descending order, or no order). Since we are looking for an exact match and we don't know if the array is sorted, we want to make sure that the match_type parameter in the MATCH function is set to 0. This will find a match regardless of the sort order.

Second, we know that the MATCH function will return an #N/A error when a match is not found, so we will want to use the ISERROR function to check for the #N/A error.

So based on these 2 additional considerations, we would want to modify your original formula as follows:

=IF(ISERROR(MATCH(B94,Overview!D$54:D$96,0))=FALSE,"FS","Bulk")

This would check for an exact match in the D54:D96 array on the Overview sheet and return "FS" if a match is found. Otherwise, it would return "Bulk" if no match is found.