MATCH Formula Excel – How to use Excel MATCH Function




TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

Syntax of MATCH Formula
Example of MATCH Formula
Possible Errors returned by the MATCH Formula

MATCH formula in Excel returns the relative position of a value in an array or a range

MATCH Formula Syntax

MATCH Formula has three parts:

MATCH (value_to_find, range_to_find_value_in, match_type)

match-formula-in-excel-use

value_to_find

value_to_find is the value which we are trying to find. It can be a number or a string. If the value_to_find is specified as a string, you can use special wildcard characters to specify the string. (For Single character use ? and for multiple characters use *)

range_to_find_value_in

range_to_find_value_in is the range in which we would like to locate the value in. This range has to consist of cells in a single continuous row of column. A 2-dimensional range will not generate the correct result.

match_type

rmatch_type is an optional parameter that specifies how we would like the match function to operate. This parameters can have three potential values -1, 0 and 1. If the value is specified as -1 then the function would try to find the smallest value in the range that is greater than or equal to the value we are trying to find. For this parameter to work correctly, the range has to be sorted in the descending order. If the value is specified as 1 then the function would try to find the largest value in the range that is less than or equal to the value we are trying to find. For this parameter to work correctly, the range has to be sorted in the ascending order. If the match_type parameter is omitted, it is assumed to be 1. Finally if the parameter is specified as 0, the first exact match to the value is returned, irrespective of the sort order of the range. (Please note that this parameter can also be a boolean with TRUE for an approximate match and FALSE for an exact match).

Also worth noting is that if a match is found, the formula returns the relative position of the value in the range which means that the position is specified with respect the first cell of the range specified and not the first cell in the spreadsheet (A1). So incase you plan to use this formula with any other function, one needs to be aware of this.

Example of a MATCH Formula

match-formula-example

Let’s us take a look at an example of the MATCH formula. Suppose we had a list of managers (names) for a business as shown in the above example. And now let’s say that we wanted to find the position at which a specific manager’s name (Say “Bill”) appears in the list. We could simply enter =MATCH(“Bill”,A1:A10,0) or=MATCH(“Bill”,A1:A10,-1). The formula mentioned first will go ahead and look for an exact match for “Bill” and if an exact match is found, will return the position (relative to the first cell in the range). The second would have tried searching for a match which is the closest value less than or equal to the value specified. Hence the chances of finding a match, all else being equal, are higher when using the later function albeit at the cost of lower accuracy.

MATCH formula with the single character (?) and multiple character (*) wildcard operators

Let’s now take the example where you would like to know the position of the first occurrence of name starting with a ‘j’ with an ‘m’ as the third character. We have two such names in the list – Jim and James. Aplhabetically James appears earlier but in our list Jim is listed at position 3 and James at 5. Now if we were to use the formula = =MATCH(“j?m”,A2:A10,0) we would get the 3 as the result. Why? Because we specified the single character wildcard (?) and 0 as the third parameter. The ? wildcard character will give a match for any single character at that position. The 0, as we’ve seen earlier, works to find and exact match.

Now say we wanted to find any name with the substring “rri” in it. We have two such names “Terri” and “Sherri” with Terri appearing first. We could use the multiple character wildcard operator (*) and write something like =MATCH(“*rri”,A2:A10,0). What we’ve done here is to specify the function to find the first occurrence where the substring “rri” occurs anywhere in the name. In this case the result will be 1 since “Terri” is a name that occurs in the first cell with the given substring. You can combine both the wildcard characters and write something like =MATCH(“?e*ri”,A2:A10,0) as a variation.

How to enter the MATCH formula in an Excel Sheet

1. Select the cell in which you want to place the formula

2. Type the formula as =MATCH(

3. Then enter the value that you would like to find. This can be a number, a string, a cell address or a combined expression with a wildcard character as shown above.

4. Press the comma key (,)

5. Then using the mouse up-down and left-right keys, move to the first cell in range in which you would like to locate this value.

6. Keeping the SHIFT key pressed, move the cursor to the last cell of the range.

7. At this point you can either close the formula by entering the closing bracket ) enter the optional third parameter. If you want to enter the third parameter, read on.

8. Press the comma key (,)

8. Enter a value from (-1,0,1) as the match_type parameters. (0 for an exact match, -1 and 1 for approximate matches as described above)

9. Close the formula by entering the closing bracket ).

Check out the clip above for knowing if the values you’ve entered are in the same order. In the end your formula should look something like this =MATCH(“jim”,A2:A10,0)

Possible Errors with the MATCH Formula

The MATCH formula can result in the following error values:

MATCH Formula #NAME? Error

If the range or a parameter has been incorrectly specified, the MATCH formula can result in the #NAME? error. For Example specifying =MATCH(“?e*ri”,A2:AAAA10,1) would lead to this error since the cell AAAA10 does not exist in the spreadsheet. Similarly specifying the match_type parameter as a string can lead to this error.

MATCH Formula #NA Error

If the MATCH formula does not find a match, it will generate the #N/A error value.

You can download an example of MATCH formula here or click on the button below:

download example of MATCH function

TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Comments and Trackbacks

  1. Jai Prabhu wrote:

    Hi,

    This formulas are very useful, i was thinking sumif, lookup are very useful, but these are amazing.

    Prabhu

  2. Ajay wrote:

    I agree. The match formula is not as popular though it is, at times just as useful as the lookups.

    Ajay

  3. Aufra wrote:

    Hi,

    Your formulas is very useful and clearly explained, please add more function.

  4. Mazhar wrote:

    Yes indeed it is very useful function and you have done very good job.

  5. Chuck S wrote:

    How do I find the lessor of 4d, 4c or 4g? These are dollar values
    in these boxes.

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel