Match Formula in Excel
- 0 Comments
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).
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.
Loading ...