Subscriber only lesson.
Sign up to this course to view this lesson.
Discover how to use MATCH functions in a formula.
When to use
MATCH(Lookup_value,Lookup_vector,[Match_type]) returns the relative position of an item in an array that (approximately) matches a specified value. It is not case sensitive.
- Match_type 1 [default if omitted]: finds the largest value less than or equal to the Lookup_value – but the Lookup_vector must be in strict ascending order, limiting flexibility;
- Match_type 0: probably the most useful setting, MATCH will find the position of the first value that matches Lookup_value exactly. The Lookup_array can have data in any order and even allows duplicates; and
- Match type -1: finds the smallest value greater than or equal to the Lookup_value – but the Lookup_array must be in strict descending order, again limiting flexibility.
When using MATCH, if there is no (approximate) match, #N/A is returned (this may also occur if data is not correctly sorted depending upon Match_type).
In the figure below, MATCH(F6,B6:B16,0) gives a value of 2, being the relative position of the first ‘1 in the range. Note that having Match_type 0 here is important. The data contains duplicates and is not sorted alphanumerically. Consequently, using Match_type 1 would give the wrong answer: #N/A
Lesson notes are only available for subscribers.