The MATCH function searches for a value in a one-dimensional range and returns its position in the row or column.
This function returns the index of an element (that is, the number of the position that the element occupies in the array), not the element itself.
The MATCH function can be useful for determining the exact location of the desired element in a range, allowing you to create dynamic and flexible formulas that automatically adjust to changes in data.
Syntax
MATCH(lookup_value, range, [lookup_method])
Argument |
Description |
Permitted values |
|---|---|---|
lookup_value |
Element to be found in the specified range |
It can be a number, text, or a Boolean value |
range |
Range (one-dimensional array: horizontal or vertical) in which the element is searched for |
Array or range of cells (for example, A1:A10) |
[lookup_method] |
(optional) Type of search used to find a number in a specified range. By default, 1 |
1, 0, −1 |
Search methods
•1: Determines the largest value that does not exceed the specified value of the argument lookup_value. In this case, the range must be sorted in ascending order: ..., −2, −1, 0, 1, 2, ..., A-Z, FALSE, TRUE.
•0: Determines the first value that corresponds to the specified lookup_value argument. The range may be unsorted.
•−1: Determines the smallest value that is greater than or equal to the specified lookup_value argument. In this case, the array we are considering must be sorted in descending order: TRUE, FALSE, Z-A, ..., 2, 1, 0, −1, −2.
Examples of use
Searching for a value in a range
If the range A1:A5 contains the values [10, 20, 30, 40, 50] and you want to find the position of the number 30:
=MATCH(30, A1:A5, 0)
The expression will return 3, since 30 is in the third position in the range.
Searching for a value with a comparison type
If you want to find the position of the number 25 in the same range using comparison type 1 (search for the smallest value that is greater than or equal to):
=MATCH(25, A1:A5, 1)
The expression will return 3, since 30 is the smallest value greater than 25, and it is in the third position.
Search when the lookup_value is not in the range
If you are looking for a value that is not contained in the array, for example, 60:
=MATCH(60, A1:A5, 0)
The expression will return the #N/A error, since 60 is not found in the range.
Notes
–The MATCH function is sensitive to data type, so text and numbers are considered different values.
–If the array is not sorted and you use lookup_method equal to 1, the function may return an incorrect result.
–The MATCH function is not case-sensitive when matching text. The values “Text” and “text” are considered equal.
–If lookup_method is 0 and lookup_value is text, lookup_value can contain wildcards: “*” and “?”.