Syntax: =match(lookup_value, lookup_array, [match_type])
Match returns the relative position of an item in a list that matches a specified value in a specified order.
The function can be broken down into three parts:
- lookup_value. What do you want to look up?
- lookup_array. Where do you want to look it up?
- [match_type]. The brackets signify an optional argument. This value can be set to 0, 1 or -1.
- 0 returns an exact match
- 1 finds the largest value that is less than or equal to the lookup_value
- -1 finds the smallest value that is greater than or equal to the lookup_value
Objective: use the match formula to find the position of the date 6/30/2013 in the excel table
- What do I want to look up? I want to look up 6/30/2013 which is located in cell E16.
- Where do I want to look it up? I want to find the value in the range B21:B33
- Exact match or not? I am looking for an exact match so I’ll set this to 0.
Result: the formula returned a value of 7. Looking in the data table I can confirm that this is the correct value.
This may not appear to be considerably useful by itself, but as you will see in the more advanced discussions, we will be embedding the match formula inside other formulas to allow for a more dynamic spreadsheet.
Click here to open the excel spreadsheet shown below: match_example