Jun 19 2007

Match Formula in Excel

The Match Formula in Excel is an easy way to have excel look through an array of values and return the relative position if the match is found. The difference between this and the lookup functions (eg. Vlookup) is it return the position of the item and not the item itself. Let’s go ahead and take a look at the formula:

Match(lookup_value, lookup_array, match_type)

where lookup_value is the value you are looking up against the array, lookup_array is the range of values you are trying to match against, and match_type tells the formula how it should do the match. Match_type can be one of three values:

1. -1 (largest value less than or equal to lookup_value, array must be in ascending order),
2. 0 (exact match of the lookup_value, array does not need to be ordered),
3. 1 (smallest value greater than or equal to lookup_value, array must be in descending order).


[ad#in-post-ad]

EXAMPLES

Example 1: =match(3,{1,3,5,7,9},0) will return 2 since it finds an exact match for 3 in the second value in the range of numbers (Using Curly Brackets {} is a way to make an array of values in a formula when you aren’t using a cell range).

Example 2: =MATCH(“Pencil”,B2:B6,0) will return 2 since Pencil is the 2nd item in the range of cells.

Example 3: =MATCH(300,A2:A6,1) will return 2 since 222 is the largest value less than the lookup_value. Notice the values in the example are in ascending order. If you try this same lookup with -1 instead of 1 in the Match_Type you will get #N/A since the values aren’t sorted correctly for that lookup.

FURTHER APPLICATION

There are many other instances in which the Match Formula can come in handy. Using it in conjunction with the Index Function or Vlookup Function can make looking of values much easier and adaptable. I will be posting examples of how this can be used in future posts.

** Excel Hints provides Excel Tips and Excel Help for All Levels of Experience.

More Excel Tips

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 

          

4 Comments on this post

Trackbacks

  1. Rick G said:

    When I “discovered” the combination of Index(array,index_row,index_col) and Match(value,comparison_array,match_type), I quit using the Vlookup and Hlookup completely. I have used Index for two-way lookups, using Match functions to determine both the column and the row of the target value. I also like the fact that the Index-Match combination doesn’t require that the “indexing” field be the first row or first column of the array; in fact it doesn’t have to be a part of the Index array at all. I’ve even had cases where my Match function looked at a comparison_array on a different sheet from that containing the array used by Index. Once learned, this two function combo is a very powerful and intuitive tool.

    March 24th, 2009 at 4:47 pm
  2. john said:

    I agree Rick. Depending on what I am doing, I use both the index/match and vlookup/hlookup formulas throughout. The index/match combination does provide a more flexible alternative if needed.

    Thanks for the info.

    John

    March 24th, 2009 at 4:53 pm
  3. Marty Brown said:

    I’m pretty proficient with vlookups, but cannot quite fathom why I would want to know where, in an array, a specific entry could be found. What is a good real world problem that using a match formula would be useful in solving?

    December 18th, 2014 at 5:39 pm
  4. ExcelHints said:

    We are in the process of adding a new extended post on VLookups, along with several videos.

    Here is a link to a one hour video we did on VLookups a few years back. http://youtu.be/5wqgS-_tygc

    January 6th, 2015 at 8:47 pm

LEAVE A COMMENT

Subscribe Form

Subscribe to Blog

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories

Archives