Home » Advanced Topics, Excel Functions, Lookup Functions

Function Match()

Submitted by on Saturday, 20 March 20105 Comments
Function Match()

Match function is used to get the relative location of a text or value within a specified array.

Syntax:

= Match (Lookup_value, Lookup_array, Lookup_type)

Lookup_value: This value in the function specifies the value or the text (an item) that is to be looked up in the array (or a range of cells)


Lookup_array: This is where one specifies the range in which the item is to be looked up. This can be a range of cells, a range name, or a reference to a range
Lookup_type: This field determines the limitations to how the value should be looked up in the array This value can be either 1, 0 or -1. In case Match Type is omitted, the default type 1 is taken. Following are the explanation to each of these values.

  • If the Match type is 1 the function will search for largest value that is less than or equal to lookup_value
  • With the Match type 0 the function gives the exact match of the lookup_value
  • MATCH finds the smallest value that is greater than or equal to lookup_value in case the Match type is -1

Examples:

For the examples below, please consider the data as shown in image below:


  • Match (“Apple”, A1:A13, 0) – the result will be: 1
  • Match (“aPpLe”, A1:A13, 0) – the result will be: 1
    • This example has lookup_value with mixed case alphabets, which is ignored by the Match Function
  • Match (B1, A1:A13, 0) – the result will be: 10
    • (for the above example, assume that cell B1 contains Cherry)
  • Match(“Cher”, A1:A13, 1) – the result will be 8
    • This function will result in 8 as the list has ‘Cherimoya’ at 9th position, which is greater than the lookup_value (please read the lookup_type section above for more clarity)

Application: Match function is used quite a lot with other Lookup functions, the most common usage being with VLookup. This function is used to make a dynamic reference to the column position from where the Lookup function should pick up the value to be returned.

Related Posts Plugin for WordPress, Blogger...
Signature

5 Comments »

  • rebeccah said:

    Hi! I have a problem regarding the VLookUp function. Here’s an example:
    a B 1
    b C 2
    c A 3

    Assuming that “a” is on A1,and “B” is on B1, how can I get the exact value of the exact letter that I enter. I used the formula =Vlookup(D1,A1:C3,3,False) on E1. When I enter “a” on D1, the result would be “1″. But if I enter “A” on D1, the result would still be “1″, but Im expecting it to be “3″. What should be done here to get the exact results?

    Thanks! Your articles have been very helpful!

    # 6 July 2010 at 8:37 pm
  • Mohit Khurana said:

    Hi Rebeccah,

    Firstly thanks for the kind words.

    For your query, I have clearly understood what you are trying to achieve here and I can tell you that it is not possible.

    Here is the reason for this function to not meet your requirements: The VLookup function ‘looks up’ the search value (in your case, the value in D1) only in the first column of the specified range in the function. In your case, the letter ‘A’ is in the second column, hence cannot be looked up.

    In case you want separate values to be returned by the function for ‘a’ and ‘A’ (and similarly other characters) you would need to mention them separately in column A itself. Here is an example:

    a 1
    b 2
    c 3
    A 4
    B 5
    C 6

    Or, in case I have understood your requirement incorrectly, please do send me an email at – Mohit.Khurana@Dhakkanz.com. I will be more than happy to solve your query.

    # 6 July 2010 at 11:40 pm
  • Rebeccah said:

    Many thanks for your quick reply!

    I tried your suggestion but it did not work. It seems that the VLookUp function looks for the first letter that matches my querry. What if I try to make it into a logical formula to make it case sensitive? I’ve tried to make a formula using =If(exact(VLookup(….. etc but I can’t seem to make it logical enough to get the results. Or is it posible to define the letters to make Vlookup case sensitive? Or, probably, Vlookup is not the correct function for that?

    Thanks! I hope you won’t get tired of answering questions.

    # 7 July 2010 at 4:47 am
  • Mohit Khurana said:

    Rebeccah,

    Try this function:

    =CHAR(VLOOKUP(CODE(D1),CODE(A1:B8),2,0))

    But for this function to work properly, you will have to use this function as an Array Function. In case you do not know about Array function, please visit these pages:

    http://www.excelmatic.dhakkanz.com/what-are-array-functions-or-array-formulas/

    http://www.excelmatic.dhakkanz.com/usage-of-array-functions/

    http://www.excelmatic.dhakkanz.com/how-to-calculate-subtotals-using-array-functions/

    Hope this helps.

    Cheers,
    Mohit

    # 7 July 2010 at 11:41 am
  • Rebeccah said:

    Thanks for your response! I busy thepast days. I’ll do what you adviced me. I’ll update you for any results.

    # 12 July 2010 at 5:18 am

Leave your response!

Add your comment below, or trackback from your own site. You can also subscribe to these comments via RSS.

Be nice. Keep it clean. Stay on topic. No spam.

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>

This is a Gravatar-enabled weblog. To get your own globally-recognized-avatar, please register at Gravatar.